Database qustion

[janorcutt]

hi, I'm writing an interface to work with database tables, and i was wondering if it were possible to add a table without the sql statement. I want to be able to call my function, lets call it

addTable(QString name, QSqlRecord rec)

addTable(QString name, QSqlRecord rec)

To copy to clipboard, switch view to plain text mode 

where the QSqlFields in 'rec' contain the field names, and types.

//so this...
QSqlQuery query("CREATE TABLE table (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT)",db);
 
// becomes
QSqlField id, data;
QSqlRecord record;
// yada yada yada
addTable("table", record);

//so this...
QSqlQuery query("CREATE TABLE table (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT)",db);

// becomes
QSqlField id, data;
QSqlRecord record;
// yada yada yada
addTable("table", record);

To copy to clipboard, switch view to plain text mode 

thanks

[janorcutt]

sorted it out with some help from the kexi source code...
heres my .h

#ifndef BACKEND_H
#define BACKEND_H
 
#include <QObject>
#include <QSqlDatabase>
#include <QSqlTableModel>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QDebug>
#include <QStringList>
#include <QList>
 
struct field {
    QString type;
    QString name;
 
    bool isKey;
    bool isNotNull;
    bool isAutoIncrement;
 
    field() : isKey(false), isNotNull(false), isAutoIncrement(false){}
 
};
 
Q_DECLARE_METATYPE(field);
 
class backend : public QObject
{
    Q_OBJECT
public:
    explicit backend(QObject *parent = 0);
    ~backend();
 
    QSqlDatabase * database() { return ptr_db; }
    bool sqlExec(QString sql);
    bool addTable(QString name, const QList<field>& fields);
    bool dropTable(QString name);
    void addRecord(QString table, QSqlRecord record, int pos = -1);
    bool isConnected() { return connected; }
    bool connectDatabase(QString connection_name, QString db_name);
    QString connectionName() { return ptr_db->connectionName(); }
 
 
signals:
 
public slots:
 
private:
    QSqlDatabase *ptr_db;
    bool connected;
};
 
#endif // BACKEND_H

#ifndef BACKEND_H
#define BACKEND_H

#include <QObject>
#include <QSqlDatabase>
#include <QSqlTableModel>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QDebug>
#include <QStringList>
#include <QList>

struct field {
    QString type;
    QString name;

    bool isKey;
    bool isNotNull;
    bool isAutoIncrement;

    field() : isKey(false), isNotNull(false), isAutoIncrement(false){}

};

Q_DECLARE_METATYPE(field);

class backend : public QObject
{
    Q_OBJECT
public:
    explicit backend(QObject *parent = 0);
    ~backend();

    QSqlDatabase * database() { return ptr_db; }
    bool sqlExec(QString sql);
    bool addTable(QString name, const QList<field>& fields);
    bool dropTable(QString name);
    void addRecord(QString table, QSqlRecord record, int pos = -1);
    bool isConnected() { return connected; }
    bool connectDatabase(QString connection_name, QString db_name);
    QString connectionName() { return ptr_db->connectionName(); }


signals:

public slots:

private:
    QSqlDatabase *ptr_db;
    bool connected;
};

#endif // BACKEND_H

To copy to clipboard, switch view to plain text mode 

and the .cpp

#include "backend.h"
 
backend::backend(QObject *parent) :
    QObject(parent)
{
    connected = false;
}
 
backend::~backend()
{
    ptr_db->close();
    ptr_db->removeDatabase(ptr_db->connectionName());
}
 
bool backend::connectDatabase(QString connection_name, QString db_name)
{
    ptr_db = new QSqlDatabase(QSqlDatabase::addDatabase("QSQLITE", connection_name));
    ptr_db->setDatabaseName(db_name);
    if (ptr_db->open()){
        connected = true;
        qDebug() << "database connection established...";
        return true;
    }
    connected = false;
    qDebug() << "unable to connect to database...";
    return false;
}
 
bool backend::dropTable(QString name)
{
    QSqlQuery query(*ptr_db);
    if (!query.exec("DROP TABLE IF EXISTS " + name)){
        return false;
    }
    qDebug() << "table" << name << "dropped from database...";
    return true;
}
 
void backend::addRecord(QString table, QSqlRecord record, int pos)
{
    QSqlTableModel model;
    model.setTable(table);
    model.database().transaction();
    model.insertRecord(pos, record);
    model.database().commit();
}
 
bool backend::sqlExec(QString sql)
{
    QSqlQuery query(sql, *ptr_db);
    if (!query.isActive()){
        return false;
    }
    return true;
}
 
bool backend::addTable(QString name, const QList<field> &fields)
{
    QByteArray s;
    bool first = true;
 
    s = "CREATE TABLE ";
    s.append(name.toLatin1() + " ( ");
 
    foreach (field f , fields){
        if (first){
            first = false;
        } else {
            s.append(", ");
        }
 
        s.append(f.name.toLatin1() + " " + f.type.toLatin1());
 
        if (f.isKey){
            s.append(" PRIMARY KEY");
        }
 
        if (f.isAutoIncrement){
            s.append(" AUTOINCREMENT");
        }
 
        if (f.isNotNull){
            s.append(" NOT NULL");
        }
    }
 
    s.append(")");
 
    QSqlQuery q(*ptr_db);
 
    if (q.exec(s)){
        return true;
    }
 
    return false;
}

#include "backend.h"

backend::backend(QObject *parent) :
    QObject(parent)
{
    connected = false;
}

backend::~backend()
{
    ptr_db->close();
    ptr_db->removeDatabase(ptr_db->connectionName());
}

bool backend::connectDatabase(QString connection_name, QString db_name)
{
    ptr_db = new QSqlDatabase(QSqlDatabase::addDatabase("QSQLITE", connection_name));
    ptr_db->setDatabaseName(db_name);
    if (ptr_db->open()){
        connected = true;
        qDebug() << "database connection established...";
        return true;
    }
    connected = false;
    qDebug() << "unable to connect to database...";
    return false;
}

bool backend::dropTable(QString name)
{
    QSqlQuery query(*ptr_db);
    if (!query.exec("DROP TABLE IF EXISTS " + name)){
        return false;
    }
    qDebug() << "table" << name << "dropped from database...";
    return true;
}

void backend::addRecord(QString table, QSqlRecord record, int pos)
{
    QSqlTableModel model;
    model.setTable(table);
    model.database().transaction();
    model.insertRecord(pos, record);
    model.database().commit();
}

bool backend::sqlExec(QString sql)
{
    QSqlQuery query(sql, *ptr_db);
    if (!query.isActive()){
        return false;
    }
    return true;
}

bool backend::addTable(QString name, const QList<field> &fields)
{
    QByteArray s;
    bool first = true;

    s = "CREATE TABLE ";
    s.append(name.toLatin1() + " ( ");

    foreach (field f , fields){
        if (first){
            first = false;
        } else {
            s.append(", ");
        }

        s.append(f.name.toLatin1() + " " + f.type.toLatin1());

        if (f.isKey){
            s.append(" PRIMARY KEY");
        }

        if (f.isAutoIncrement){
            s.append(" AUTOINCREMENT");
        }

        if (f.isNotNull){
            s.append(" NOT NULL");
        }
    }

    s.append(")");

    QSqlQuery q(*ptr_db);

    if (q.exec(s)){
        return true;
    }

    return false;
}

To copy to clipboard, switch view to plain text mode