contactengine/dbtools.cpp
author Sebastian Brannstrom <sebastianb@symbian.org>
Mon, 09 Aug 2010 14:37:31 +0100
changeset 20 a7451a8eb5dc
child 21 3bfc3227045d
permissions -rw-r--r--
Added DB framework for CSV import - doesn't work yet though
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
20
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
     1
#include <QtCore/QCoreApplication>
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
     2
#include <QtSql>
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
     3
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
     4
#include "database.h"
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
     5
#include "dbtools.h"
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
     6
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
     7
bool DBTools::createTable(QString sqlStmt)
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
     8
{
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
     9
    QSqlQuery query;
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    10
    return query.exec(sqlStmt);
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    11
}
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    12
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    13
int DBTools::getLastInsertRowId()
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    14
{
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    15
    int rc = -1;
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    16
    QSqlQuery query;
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    17
    query.exec("select last_insert_rowid()");
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    18
    if (!query.exec()) {
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    19
        QString errCode =  "last rowid query Failed: " + query.lastError().text();
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    20
        qWarning(errCode.toStdString().c_str());
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    21
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    22
    } else {
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    23
        query.next();
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    24
        rc = query.value(0).toInt();
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    25
    }
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    26
    return rc;
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    27
}
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    28
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    29
void DBTools::insertContact( QString name, QString mobile, QString deskphone,
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    30
                    int xtn, QString email, QString skype,
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    31
                    QString twitter, QString title, int site,
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    32
                    int department, int floor)
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    33
{
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    34
    QSqlQuery query;
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    35
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    36
    query.prepare("INSERT INTO contacts ( name, mobile, deskphone, xtn, "
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    37
                  "email, skype, twitter, title, site, department, floor) "
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    38
                  "VALUES (:name,:mobile,:deskphone,:xtn,:email,:skype, "
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    39
                  ":twitter, :title, :site, :department, :floor)");
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    40
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    41
    query.bindValue(":name", name);
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    42
    query.bindValue(":mobile", mobile);
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    43
    query.bindValue(":deskphone", deskphone);
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    44
    query.bindValue(":xtn", xtn);
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    45
    query.bindValue(":twitter", twitter);
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    46
    query.bindValue(":skype", skype);
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    47
    query.bindValue(":email", email);
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    48
    query.bindValue(":title", title);
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    49
    query.bindValue(":site", site);
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    50
    query.bindValue(":department", department);
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    51
    query.bindValue(":floor", floor);
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    52
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    53
    if (!query.exec())
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    54
    {
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    55
        qDebug() << query.lastError();
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    56
        qFatal("Failed to add fish.");
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    57
    }
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    58
}
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    59
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    60
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    61
bool DBTools::createDB()
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    62
{
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    63
    QSqlDatabase db;
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    64
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    65
    // Find QSLite driver
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    66
    db = QSqlDatabase::addDatabase("QSQLITE");
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    67
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    68
    db.setDatabaseName("C:\\workspace\\QtExamples\\contactengine\\contactengine.db");
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    69
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    70
    // Open databasee
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    71
    if(!db.open())
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    72
    {
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    73
        qDebug() << "DB: failed to open" << endl;
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    74
        exit (1);
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    75
    }
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    76
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    77
    qDebug() << "DB: database opened " << endl;
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    78
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    79
    bool rc = createTable("create table contacts "
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    80
                "(cid integer primary key, "
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    81
                "name varchar(128),"
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    82
                "mobile varchar(128),"
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    83
                "deskphone varchar(128),"
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    84
                "xtn int,"
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    85
                "email varchar(128),"
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    86
                "skype varchar(128),"
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    87
                "twitter varchar(128),"
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    88
                "title varchar(128),"
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    89
                "site int,"
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    90
                "department int,"
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    91
                "floor int");
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    92
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    93
    return rc;
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    94
}
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    95
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    96
void DBTools::testDB()
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    97
{
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    98
    createDB();
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
    99
    insertContact("Tom", "12345", "45678", 22, "tom@symbian.org", "thetom", "tomtom",
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   100
                  "Senior Tom", SITE_LONDON, DEPT_TDM, 0);
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   101
}
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   102
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   103
void DBTools::importCSV(QString fileName)
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   104
{
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   105
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   106
}
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   107
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   108
DBTools::DBTools(QObject *parent)
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   109
{
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   110
    QObject();
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   111
}
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   112
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   113
DBTools::~DBTools()
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   114
{
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   115
a7451a8eb5dc Added DB framework for CSV import - doesn't work yet though
Sebastian Brannstrom <sebastianb@symbian.org>
parents:
diff changeset
   116
}