panthema / 2006 / qtsqlview / src / QtSqlView-0.8.0 / src / DbConnection.h (Download File)
// $Id: DbConnection.h 20 2006-10-10 08:24:36Z tb $
// Copyright 2006 Timo Bingmann
//
// Contains the classes for the database connection listing, the subtrees
// showing the containing tables and the schema display model.
//

#ifndef _DbConnection_H_
#define _DbConnection_H_

#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlError>
#include <QtSql/QSqlTableModel>
#include <QtSql/QSqlRecord>
#include <QtSql/QSqlField>
#include <QtSql/QSqlIndex>
#include <QtSql/QSqlQuery>

#include <QtGui/QIcon>
#include <QtGui/QHeaderView>
#include <QtGui/QMessageBox>
#include <QtGui/QInputDialog>

#include <QtCore/QAbstractItemModel>
#include <QtCore/QVector>
#include <QtCore/QSettings>
#include <QtCore/QUuid>

#include "DbTypes.h"

struct DbParameter
{
    QString	label;
    QString	hostname;
    int		port;
    QString	driver;
    QString	username;
    QString	password;
    int		askpassword;
    QString	database;
    int		showsystables;

    void saveToSettings(QSettings &settings) const
    {
	settings.setValue("label", label);
	settings.setValue("hostname", hostname);
	settings.setValue("port", port);
	settings.setValue("driver", driver);
	settings.setValue("username", username);
	settings.setValue("password", password);
	settings.setValue("askpassword", askpassword);
	settings.setValue("database", database);
	settings.setValue("showsystables", showsystables);
    }

    void loadFromSettings(QSettings &settings)
    {
	label = settings.value("label").toString();
	hostname = settings.value("hostname").toString();
	port = settings.value("port", 0).toUInt();
	driver = settings.value("driver").toString();
	username = settings.value("username").toString();
	password = settings.value("password").toString();
	askpassword = settings.value("askpassword", 0).toUInt();
	database = settings.value("database").toString();
	showsystables = settings.value("showsystables", 0).toUInt();
    }
};

class DbTable : public QObject
{
    Q_OBJECT

public:
    /// link to parent connection object
    class DbConnection *dbconn;

    /// guess
    QString	tablename;

    /// 0 = user table, 1 = view, 2 = system table
    int 	tabletype;

    inline DbTable(class DbConnection *dbc, const QString &tab, int _tabletype)
	: QObject(), dbconn(dbc), tablename(tab), tabletype(_tabletype)
    {
    }
};

class DbError : public QObject, public QSqlError
{
    Q_OBJECT

public:
    /// link to parent connection object
    class DbConnection *dbconn;

    DbError(class DbConnection *_dbconn)
	: QObject(), QSqlError(), dbconn(_dbconn)
    {
    }

    DbError(const QSqlError &e)
	: QObject(), QSqlError(e)
    {
    }

    DbError& operator=(const QSqlError &e)
    {
	*(QSqlError*)this = e;
	return *this;
    }
};

class DbConnection : public QObject
{
    Q_OBJECT

public:
    /// parameters of the connection
    DbParameter		dbparam;

    /// because qt requires us to use addDatabase with a unique id, we use the
    /// only random function aviablable in qt itself here.
    QUuid		dbuuid;

    QSqlDatabase	db;

    DbError		connecterror;

    DbConnection(const DbParameter &params)
	: QObject(), dbparam(params),
	  dbuuid( QUuid::createUuid() ),
	  connecterror(this)
    {
    }

    QSqlError	connect(class DbList &dblist);

    void 	disconnect(class DbList &dblist);

    typedef QVector<DbTable*> tablelist_t;

    QVector<DbTable*>	tablelist;

    inline int numChildren() const
    {
	if (db.isOpen())
	    return tablelist.size();
	else if (connecterror.isValid())
	    return 2;
	else
	    return 0;
    }
};

class DbList : public QAbstractItemModel
{
    Q_OBJECT

public:
    inline ~DbList()
    {
	clear();
    }

    /// the primary list of data connections
    QVector<DbConnection*> 	list;

    /// correctly delete all dbconnections
    void clear()
    {
	if (!list.isEmpty())
	{
	    // first delete children, then the db entries. otherwise the model
	    // goes crazy
	    Q_FOREACH(DbConnection *dbc, list)
	    {
		dbc->disconnect(*this);
	    }

	    beginRemoveRows(QModelIndex(), 0, list.size()-1);

	    Q_FOREACH(DbConnection *dbc, list)
	    {
		delete dbc;
	    }
	    list.clear();

	    endRemoveRows();
	}
    }

    /// add a new dbconnection
    void addDbConnection(DbParameter &dbp)
    {
	beginInsertRows(QModelIndex(), list.size(), list.size());
	
	list.push_back( new DbConnection(dbp) );
	
	endInsertRows();
    }

    /// change a dbconnection
    void editDbConnection(int num, DbParameter &dbp)
    {
	if (0 > num || num >= list.size()) return;

	// close old connection and change parameters
	list[num]->disconnect(*this);

	list[num]->dbparam = dbp;

	emit dataChanged(createIndex(num, 0, list[num]),
			 createIndex(num, 0, list[num]));
    }

    /// remove a dbconnection
    void delDbConnection(int num)
    {
	if (0 > num || num >= list.size()) return;

	beginRemoveRows(QModelIndex(), num, num);

	list[num]->disconnect(*this);
	delete list[num];

	list.remove(num);

	endRemoveRows();
    }

    /// save all database parameters to settings
    void saveToSettings()
    {
	QSettings settings ("idlebox.net", "QtSqlView");

	settings.beginWriteArray("connections");
	int i = 0;
	Q_FOREACH(const DbConnection *dbc, list)
	{
	    settings.setArrayIndex(i++);

	    dbc->dbparam.saveToSettings(settings);
	}
	settings.endArray();
    }

    /// load all database parameters from settings
    void loadFromSettings()
    {
	QSettings settings ("idlebox.net", "QtSqlView");

	clear();

	int connnum = settings.beginReadArray("connections");
	for (int i = 0; i < connnum; ++i)
	{
	    settings.setArrayIndex(i);

	    DbParameter dbp;
	    dbp.loadFromSettings(settings);

	    list.push_back( new DbConnection(dbp) );
	}
	settings.endArray();
    }

public: // *** implements QAbstractItemModel

    // *** Columns

    virtual int columnCount(const QModelIndex &) const
    {
	return 1;
    }

    virtual QVariant headerData(int section, Qt::Orientation orientation, int role = Qt::DisplayRole) const
    {
	if (orientation == Qt::Horizontal)
	{
	    if (role == Qt::DisplayRole)
	    {
		switch(section)
		{
		case 0:
		    return "Connections";
		default:
		    return QString("Column %1").arg(section);
		}
	    }
	    else
		return QVariant();
	}
	else
	{
	    if (role != Qt::DisplayRole)
		return QVariant();
	    
	    return QString("Row %1").arg(section);
	}
    }

    // *** Rows

    virtual int rowCount(const QModelIndex &index) const
    {
	if (!index.isValid())
	    return list.size();
	else
	{
	    QObject *obj = (QObject*)index.internalPointer();

	    if (const DbConnection *dbc = qobject_cast<const DbConnection*>(obj))
	    {
		return dbc->numChildren();
	    }
	}

	return 0;
    }

    virtual bool hasChildren(const QModelIndex& parent = QModelIndex()) const
    {
	if (!parent.isValid())
	    return true;

	QObject *obj = (QObject*)parent.internalPointer();

	if (const DbConnection *dbc = qobject_cast<const DbConnection*>(obj))
	{
	    dbc++;
	    return true;
	}
	
	return false;
    }
    
    virtual QVariant data(const QModelIndex &index, int role = Qt::DisplayRole) const
    {
	if (!index.isValid())
	    return QVariant();

	QObject *obj = (QObject*)index.internalPointer();

	if (const DbConnection *dbc = qobject_cast<const DbConnection*>(obj))
	{
	    if (role == Qt::DisplayRole)
	    {
		if (dbc->dbparam.label.isEmpty()) {
		    return "<no label>";
		}

		return dbc->dbparam.label;
	    }
	    else if (role == Qt::DecorationRole)
	    {
		static QIcon dbicon(":/img/database.png");
		return qVariantFromValue(dbicon);
	    }
	}
	else if (const DbTable *dbt = qobject_cast<const DbTable*>(obj))
	{
	    if (role == Qt::DisplayRole)
	    {
		return dbt->tablename;
	    }
	    else if (role == Qt::DecorationRole)
	    {
		static QIcon tableicon(":/img/table.png");
		static QIcon tablesysicon(":/img/tablesys.png");
		static QIcon viewicon(":/img/view.png");

		if (dbt->tabletype == 0)
		    return qVariantFromValue(tableicon);
		else if (dbt->tabletype == 1)
		    return qVariantFromValue(viewicon);
		else if (dbt->tabletype == 2)
		    return qVariantFromValue(tablesysicon);
	    }
	}
	else if (const DbError *err = qobject_cast<const DbError*>(obj))
	{
	    if (role == Qt::DisplayRole)
	    {
		if (index.row() == 0)
		    return err->driverText();
		else if (index.row() == 1)
		    return err->databaseText();
	    }
	    else if (role == Qt::DecorationRole)
	    {
		static QIcon erroricon(":/img/error.png");
		return qVariantFromValue(erroricon);
	    }
	}

	return QVariant();
    }
    
    virtual QModelIndex index(int row, int column, const QModelIndex& parent = QModelIndex()) const
    {
	if (!parent.isValid()) {
	    if (row < list.size())
		return createIndex(row, column, list[row]);
	}
	else
	{
	    QObject *obj = (QObject*)parent.internalPointer();

	    if (const DbConnection *dbc = qobject_cast<const DbConnection*>(obj))
	    {
		if (dbc->db.isOpen())
		{
		    if (row < dbc->tablelist.size())
			return createIndex(row, column, dbc->tablelist[row]);
		}
		else if (dbc->connecterror.isValid())
		{
		    return createIndex(row, column, (void*)&dbc->connecterror);
		}
	    }
	}
	return QModelIndex();
    }

    virtual QModelIndex parent(const QModelIndex& index) const
    {
	if (!index.isValid())
	    return QModelIndex();

	QObject *obj = (QObject*)index.internalPointer();

	if (const DbConnection *dbc = qobject_cast<const DbConnection*>(obj))
	{
	    dbc++;
	    // table entries have the root as parent
	    return QModelIndex();
	}
	else if (const DbTable *dbt = qobject_cast<const DbTable*>(obj))
	{
	    return createIndex(list.indexOf(dbt->dbconn), 0, dbt->dbconn);
	}
	else if (const DbError *err = qobject_cast<const DbError*>(obj))
	{
	    return createIndex(list.indexOf(err->dbconn), 0, err->dbconn);
	}
	return QModelIndex();
    }

    int getDbConnectionNum(const QModelIndex &index) const
    {
	if (!index.isValid()) return -1;

	QObject *obj = (QObject*)index.internalPointer();

	if (DbConnection *dbc = qobject_cast<DbConnection*>(obj))
	{
	    return list.indexOf(dbc);
	}
	else if (const DbTable *dbt = qobject_cast<const DbTable*>(obj))
	{
	    return list.indexOf(dbt->dbconn);
	}
	return 0;
    }

    DbConnection* getDbConnection(const QModelIndex &index) const
    {
	if (!index.isValid()) return NULL;

	QObject *obj = (QObject*)index.internalPointer();

	if (DbConnection *dbc = qobject_cast<DbConnection*>(obj))
	{
	    return dbc;
	}
	else if (DbTable *dbt = qobject_cast<DbTable*>(obj))
	{
	    return dbt->dbconn;
	}
	return NULL;
    }
 
    DbTable* getDbTable(const QModelIndex &index) const
    {
	if (!index.isValid()) return NULL;

	QObject *obj = (QObject*)index.internalPointer();

	if (DbTable *dbt = qobject_cast<DbTable*>(obj))
	{
	    return dbt;
	}
	return NULL;
    }
  
    void expanding(const QModelIndex &index)
    {
	if (!index.isValid()) return;

	QObject *obj = (QObject*)index.internalPointer();

	if (DbConnection *dbc = qobject_cast<DbConnection*>(obj))
	{
	    if (!dbc->db.isOpen())
	    {
		dbc->connect(*this);
	    }
	}
    }

    void collapsed(const QModelIndex &index)
    {
	if (!index.isValid()) return;

	QObject *obj = (QObject*)index.internalPointer();

	if (DbConnection *dbc = qobject_cast<DbConnection*>(obj))
	{
	    if (dbc->db.isOpen())
	    {
		dbc->disconnect(*this);
	    }
	}
    }

    void refresh()
    {
	Q_FOREACH(DbConnection *dbc, list)
	{
	    if (dbc->db.isOpen())
	    {
		tablelist_clear(*dbc);
		
		tablelist_load(*dbc);
	    }
	}
    }

    // *** Tablelist Functions are located here, because they have to emit signals

    void tablelist_clear(class DbConnection &dbc)
    {
	if (!dbc.tablelist.isEmpty())
	{
	    beginRemoveRows(createIndex(list.indexOf(&dbc), 0, &dbc),
			    0, dbc.tablelist.size()-1);
	
	    Q_FOREACH(DbTable *t, dbc.tablelist)
	    {
		delete t;
	    }
	    dbc.tablelist.clear();

	    endRemoveRows();
	}
	if (dbc.connecterror.isValid())
	{
	    beginRemoveRows(createIndex(list.indexOf(&dbc), 0, &dbc),
			    0, 1);


	    dbc.connecterror = QSqlError();

	    endRemoveRows();
	}
    }

    void tablelist_load(class DbConnection &dbc)
    {
	tablelist_clear(dbc);

	if (!dbc.db.isOpen()) return;

	QVector<DbTable*>	newtablelist;

	Q_FOREACH(QString table, dbc.db.tables())
	{
	    newtablelist.push_back( new DbTable(&dbc, table, 0) );
	}

	Q_FOREACH(QString table, dbc.db.tables(QSql::Views))
	{
	    newtablelist.push_back( new DbTable(&dbc, table, 1) );
	}

	if (dbc.dbparam.showsystables)
	{
	    Q_FOREACH(QString table, dbc.db.tables(QSql::SystemTables))
	    {
		newtablelist.push_back( new DbTable(&dbc, table, 2) );
	    }
	}

	beginInsertRows(createIndex(list.indexOf(&dbc), 0, &dbc),
			0, newtablelist.size()-1);

	dbc.tablelist = newtablelist;

	endInsertRows();
    }

    void tablelist_seterror(class DbConnection &dbc, QSqlError e)
    {
	tablelist_clear(dbc);

	beginInsertRows(createIndex(list.indexOf(&dbc), 0, &dbc),
			0, 1);

	dbc.connecterror = e;

	endInsertRows();
    }
};

class DbSchemaModel : public QAbstractTableModel
{
    Q_OBJECT

private:
    /// the record of which the schema is displayed
    QSqlRecord	record;

    /// the database driver name
    QString	driver;

    /// the primary index of the table
    QSqlIndex	pindex;

public:
    /// change the data in the model
    void setRecord(const DbTable *dbt, QSqlRecord r)
    {
	record = r;
	driver = dbt->dbconn->db.driverName();
	pindex = dbt->dbconn->db.primaryIndex(dbt->tablename);

	emit layoutChanged();
    }

public: // *** implements QAbstractItemModel

    // *** Columns

    virtual int columnCount(const QModelIndex &) const
    {
	return 5;
    }

    virtual QVariant headerData(int section, Qt::Orientation orientation, int role = Qt::DisplayRole) const
    {
	if (orientation == Qt::Horizontal)
	{
	    if (role == Qt::DisplayRole)
	    {
		switch(section)
		{
		case 0:
		    return "Connections";
		case 1:
		    return "Type";
		case 2:
		    return "Length";
		case 3:
		    return "Modifiers";
		case 4:
		    return "Default";
		default:
		    return QString("Column %1").arg(section);
		}
	    }
	}
	return QVariant();
    }

    // *** Rows

    virtual int rowCount(const QModelIndex &) const
    {
	return record.count();
    }

    virtual QVariant data(const QModelIndex &index, int role = Qt::DisplayRole) const
    {
	if (!index.isValid())
	    return QVariant();

	if (role == Qt::DisplayRole)
	{
	    if (index.row() >= record.count())
		return QVariant();

	    QSqlField field = record.field(index.row());

	    switch(index.column())
	    {
	    case 0:
		return field.name();

	    case 1:
		return DbTypes::getName(driver, field.typeID());

	    case 2:
		return field.length();

	    case 3: {
		QString mods = "";

		if (pindex.indexOf(field.name()) >= 0) {
		    mods += "PRIMARY KEY";
		}

		if (field.requiredStatus() == QSqlField::Required)
		{
		    if (!mods.isEmpty()) mods += " ";
		    mods += "NOT NULL";
		}
		else if (field.requiredStatus() == QSqlField::Optional)
		{
		    if (!mods.isEmpty()) mods += " ";
		    mods += "NULL";
		}

		if (field.isAutoValue())
		{
		    if (!mods.isEmpty()) mods += " ";
		    mods += "AUTO_INCREMENT";
		}

		return mods;
	    }

	    case 4:
		return field.defaultValue();
	    }
	}
	else if (role == Qt::DecorationRole)
	{
	}

	return QVariant();
    }
};

#endif // _DbConnection_H_