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) 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);

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


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;
}