#include <QtSql>
#include <QtGui>
{
Q_OBJECT
public:
EditableSqlModel
(QObject * parent
= 0);
Qt
::ItemFlags flags
( const QModelIndex & index
) const;
void sort ( int column, Qt::SortOrder order = Qt::AscendingOrder );
private:
void refreshOffsets();
void resizeVectors();
QHash<QString, QString> hash;
int rows;
QVector<int> rowOffsets;
QVector<bool> available;
enum { UNAVAILABLE = 1, AVAILABLE = 0 };
};
{
Q_OBJECT
public:
public slots:
void deleteEntry();
private:
EditableSqlModel model;
};
#include <QtSql>
#include <QtGui>
class EditableSqlModel : public QAbstractItemModel
{
Q_OBJECT
public:
EditableSqlModel(QObject * parent = 0);
bool setData ( const QModelIndex & index, const QVariant & value, int role = Qt::EditRole );
Qt::ItemFlags flags ( const QModelIndex & index ) const;
QVariant data ( const QModelIndex & item, int role = Qt::DisplayRole ) const;
int rowCount ( const QModelIndex & parent = QModelIndex() ) const;
void sort ( int column, Qt::SortOrder order = Qt::AscendingOrder );
bool removeRows ( int row, int count, const QModelIndex & parent = QModelIndex() );
QModelIndex index ( int row, int column, const QModelIndex & parent = QModelIndex() ) const;
bool hasIndex ( int row, int column, const QModelIndex & parent = QModelIndex() ) const;
QModelIndex parent(const QModelIndex &child) const;
int columnCount(const QModelIndex &parent = QModelIndex()) const;
void setQuery(QString sql);
private:
void refreshOffsets();
void resizeVectors();
QString lastSql;
QSqlQueryModel mod;
QHash<QString, QString> hash;
int rows;
QString sortField, ordering;
QVector<int> rowOffsets;
QVector<bool> available;
enum { UNAVAILABLE = 1, AVAILABLE = 0 };
};
class MainWindow : public QMainWindow
{
Q_OBJECT
public:
MainWindow(QWidget * parent = 0);
public slots:
void deleteEntry();
private:
EditableSqlModel model;
QAction deleteAction;
QTableView tv;
};
To copy to clipboard, switch view to plain text mode
Source:
#include <QtGui>
#include "test11.h"
EditableSqlModel
::EditableSqlModel(QObject * parent
) :{}
{
Q_UNUSED(child);
}
int EditableSqlModel
::columnCount(const QModelIndex &parent
) const {
if (parent.isValid())
return 0;
return 3;
}
bool EditableSqlModel
::hasIndex ( int row,
int column,
const QModelIndex & parent
) const {
if (column < 0 || column > 2 || row < 0 || row >= rowCount() || parent.isValid())
return false;
return true;
}
{
return hasIndex
(row, column, parent
) ? createIndex
(row, column,
0) : QModelIndex();
}
{
if (!item.isValid() || !hasIndex(item.row(), item.column(), item.parent()))
if (item.row() == rowCount() - 1)
return QVariant();
/* Our empty row for inserts. */
/* Adjust for deleted rows. */
int row = item.row() + rowOffsets.at(item.row());
if (role == Qt::DisplayRole || role == Qt::EditRole)
{
if (hash.
contains(where
) || hash.
contains(QString("%1_0").
arg(row
))) original = hash.value(where); /* New data from the hash. */
else
original = mod.data(mod.index(row, item.column()), role).toString(); /* Old data from the original model. */
if (item.column() == 1)
{
/* Custom formatting goes here. */
original = original.prepend('$');
}
return original;
}
}
bool EditableSqlModel
::setData ( const QModelIndex & itemIndex,
const QVariant & value,
int role
) {
if (!itemIndex.isValid() || !hasIndex(itemIndex.row(), itemIndex.column(), itemIndex.parent()))
return false;
if (role != Qt::DisplayRole && role != Qt::EditRole)
return false;
bool fInserted;
/* Adjust for deleted rows. */
int row = itemIndex.row() + rowOffsets.at(itemIndex.row());
if (itemIndex.row() == rowCount() - 1 &&
!hash.
contains(QString("%1_0").
arg(row
))) {
/* A new record, so insert it into the database. */
QString first
= itemIndex.
column() == 1 ? value.
toString() : "";
QString last
= itemIndex.
column() == 2 ? value.
toString() : "";
query.prepare("INSERT INTO T1(First, Last) VALUES(:first, :last)");
query.bindValue(":first", first);
query.bindValue(":last", last);
query.exec();
/* Store our primary key. */
hash.insert(where, query.lastInsertId().toString());
beginInsertRows(itemIndex.parent(), itemIndex.row() + 1, itemIndex.row() + 1);
rows = rowCount() + 1;
resizeVectors();
fInserted = true;
}
else
{
/* An existing record, so update it in the database. */
int primaryKey = data(index(itemIndex.row(), 0), Qt::DisplayRole).toInt();
if (itemIndex.column() == 1)
{
query.prepare("UPDATE T1 SET First=:first WHERE id = :id");
query.bindValue(":first", value);
query.bindValue(":id", primaryKey);
query.exec();
}
else if (itemIndex.column() == 2)
{
query.prepare("UPDATE T1 SET Last=:last WHERE id = :id");
query.bindValue(":last", value);
query.bindValue(":id", primaryKey);
query.exec();
}
}
/* Store the new value in a hash to retrieve later. */
hash.insert(where, value.toString());
if (fInserted)
endInsertRows();
return true;
}
int EditableSqlModel
::rowCount ( const QModelIndex & parent
) const {
return parent.isValid() ? 0 : rows;
}
Qt
::ItemFlags EditableSqlModel
::flags ( const QModelIndex & index
) const{
Q_UNUSED(index);
return Qt::ItemIsSelectable | Qt::ItemIsEnabled | Qt::ItemIsEditable;
}
void EditableSqlModel
::setQuery(QString sql
) {
if (sql == lastSql) return;
beginResetModel();
endRemoveRows();
mod.setQuery(sql);
rows = mod.rowCount() + 1;
resizeVectors();
hash.clear();
qFill(rowOffsets, 0);
qFill(available, 0);
endInsertRows();
endResetModel();
lastSql = sql;
}
void EditableSqlModel::sort ( int column, Qt::SortOrder order)
{
sortField = column == 1 ? "First" : "Last";
ordering = order == Qt::AscendingOrder ? "ASC" : "DESC";
setQuery
(QString("SELECT id, First, Last FROM T1 ORDER BY %1 %2").
arg(sortField, ordering
));
}
void EditableSqlModel::resizeVectors()
{
rowOffsets.resize(qMax(rowOffsets.count() + 1, rows));
available.resize(qMax(available.count() + 1, rows));
refreshOffsets();
}
void EditableSqlModel::refreshOffsets()
{
/* Now calculate the offsets. */
for (int i = 0, j = 0; i < available.count(); i++)
{
if (available.at(i) == AVAILABLE)
{
rowOffsets[j] = i - j;
j++;
}
}
}
bool EditableSqlModel
::removeRows ( int row,
int count,
const QModelIndex & parent
) {
beginRemoveRows(parent, row, row + count - 1);
if (row == rowCount() - 1)
{
/* Don't let the new addition row be deleted. */
endRemoveRows();
beginInsertRows(parent, row, row);
endInsertRows();
return false;
}
query.prepare("DELETE FROM T1 WHERE id=:id");
for (int i = row + count - 1; i >= row; i--)
{
query.bindValue(":id", data(index(i, 0)).toInt());
query.exec();
}
/* Get real rows deleted and mark as deleted. */
for (int i = row; i < row + count; i++)
{
int rowreal = i + rowOffsets.at(i);
available[rowreal] = UNAVAILABLE;
hash.
remove(QString("%1_0").
arg(rowreal
));
hash.
remove(QString("%1_1").
arg(rowreal
));
hash.
remove(QString("%1_2").
arg(rowreal
));
}
refreshOffsets();
rows -= count;
endRemoveRows();
return true;
}
void MainWindow::deleteEntry()
{
QModelIndexList idxs = tv.selectionModel()->selectedRows();
/* Must do this one at a time or sort and go backwards. */
while (!idxs.isEmpty())
{
tv.model()->removeRow(idxs.at(0).row(), idxs.at(0).parent());
idxs = tv.selectionModel()->selectedRows();
}
}
MainWindow
::MainWindow(QWidget *parent
) : QMainWindow(parent
), deleteAction
("Delete",
this), tv
(this) {
model.setQuery("SELECT id, First, Last FROM T1");
tv.setModel(&model);
tv.setColumnHidden(0, true); /* Hide primary key */
tv.setSortingEnabled(true);
tv.addAction(&deleteAction);
tv.setContextMenuPolicy(Qt::ActionsContextMenu);
connect(&deleteAction, SIGNAL(triggered()), this, SLOT(deleteEntry()));
setCentralWidget(&tv);
tv.show();
}
{
db.setDatabaseName(":memory:");
db.open();
QString sql
= QString("CREATE TABLE T1(id INTEGER PRIMARY KEY AUTOINCREMENT, First, Last)");
query.exec(sql);
db.transaction();
query.prepare("INSERT INTO T1 (First, Last) "
"VALUES (:2, :3)");
for (int i = 0; i < 5; i++)
{
query.
bindValue(":2",
QString("Bart %1").
arg(i
));
query.bindValue(":3", "Simpson");
query.exec();
}
db.commit();
}
int main(int argc, char *argv[])
{
setupDb(db);
MainWindow win(0);
win.show();
return a.exec();
}
#include <QtGui>
#include "test11.h"
EditableSqlModel::EditableSqlModel(QObject * parent) :
QAbstractItemModel(parent), rows(1), sortField("First"), ordering("ASC")
{}
QModelIndex EditableSqlModel::parent(const QModelIndex &child) const
{
Q_UNUSED(child);
return QModelIndex();
}
int EditableSqlModel::columnCount(const QModelIndex &parent) const
{
if (parent.isValid())
return 0;
return 3;
}
bool EditableSqlModel::hasIndex ( int row, int column, const QModelIndex & parent ) const
{
if (column < 0 || column > 2 || row < 0 || row >= rowCount() || parent.isValid())
return false;
return true;
}
QModelIndex EditableSqlModel::index ( int row, int column, const QModelIndex & parent ) const
{
return hasIndex(row, column, parent) ? createIndex(row, column, 0) : QModelIndex();
}
QVariant EditableSqlModel::data ( const QModelIndex & item, int role ) const
{
if (!item.isValid() || !hasIndex(item.row(), item.column(), item.parent()))
return QVariant();
if (item.row() == rowCount() - 1)
return QVariant(); /* Our empty row for inserts. */
/* Adjust for deleted rows. */
int row = item.row() + rowOffsets.at(item.row());
if (role == Qt::DisplayRole || role == Qt::EditRole)
{
QString where = QString("%1_%2").arg(row).arg(item.column());
QString original;
if (hash.contains(where) || hash.contains(QString("%1_0").arg(row)))
original = hash.value(where); /* New data from the hash. */
else
original = mod.data(mod.index(row, item.column()), role).toString(); /* Old data from the original model. */
if (item.column() == 1)
{
/* Custom formatting goes here. */
original = original.prepend('$');
}
return original;
}
return QVariant();
}
bool EditableSqlModel::setData ( const QModelIndex & itemIndex, const QVariant & value, int role )
{
if (!itemIndex.isValid() || !hasIndex(itemIndex.row(), itemIndex.column(), itemIndex.parent()))
return false;
if (role != Qt::DisplayRole && role != Qt::EditRole)
return false;
QSqlQuery query;
bool fInserted;
/* Adjust for deleted rows. */
int row = itemIndex.row() + rowOffsets.at(itemIndex.row());
if (itemIndex.row() == rowCount() - 1 &&
!hash.contains(QString("%1_0").arg(row)))
{
/* A new record, so insert it into the database. */
QString first = itemIndex.column() == 1 ? value.toString() : "";
QString last = itemIndex.column() == 2 ? value.toString() : "";
query.prepare("INSERT INTO T1(First, Last) VALUES(:first, :last)");
query.bindValue(":first", first);
query.bindValue(":last", last);
query.exec();
/* Store our primary key. */
QString where = QString("%1_0").arg(row);
hash.insert(where, query.lastInsertId().toString());
beginInsertRows(itemIndex.parent(), itemIndex.row() + 1, itemIndex.row() + 1);
rows = rowCount() + 1;
resizeVectors();
fInserted = true;
}
else
{
/* An existing record, so update it in the database. */
int primaryKey = data(index(itemIndex.row(), 0), Qt::DisplayRole).toInt();
if (itemIndex.column() == 1)
{
query.prepare("UPDATE T1 SET First=:first WHERE id = :id");
query.bindValue(":first", value);
query.bindValue(":id", primaryKey);
query.exec();
}
else if (itemIndex.column() == 2)
{
query.prepare("UPDATE T1 SET Last=:last WHERE id = :id");
query.bindValue(":last", value);
query.bindValue(":id", primaryKey);
query.exec();
}
}
/* Store the new value in a hash to retrieve later. */
QString where = QString("%1_%2").arg(row).arg(itemIndex.column());
hash.insert(where, value.toString());
if (fInserted)
endInsertRows();
return true;
}
int EditableSqlModel::rowCount ( const QModelIndex & parent) const
{
return parent.isValid() ? 0 : rows;
}
Qt::ItemFlags EditableSqlModel::flags ( const QModelIndex & index ) const
{
Q_UNUSED(index);
return Qt::ItemIsSelectable | Qt::ItemIsEnabled | Qt::ItemIsEditable;
}
void EditableSqlModel::setQuery(QString sql)
{
if (sql == lastSql) return;
beginResetModel();
beginRemoveRows(QModelIndex(), 0, rowCount() - 1);
endRemoveRows();
mod.setQuery(sql);
rows = mod.rowCount() + 1;
resizeVectors();
beginInsertRows(QModelIndex(), 0, rows - 1);
hash.clear();
qFill(rowOffsets, 0);
qFill(available, 0);
endInsertRows();
endResetModel();
lastSql = sql;
}
void EditableSqlModel::sort ( int column, Qt::SortOrder order)
{
sortField = column == 1 ? "First" : "Last";
ordering = order == Qt::AscendingOrder ? "ASC" : "DESC";
setQuery(QString("SELECT id, First, Last FROM T1 ORDER BY %1 %2").arg(sortField, ordering));
}
void EditableSqlModel::resizeVectors()
{
rowOffsets.resize(qMax(rowOffsets.count() + 1, rows));
available.resize(qMax(available.count() + 1, rows));
refreshOffsets();
}
void EditableSqlModel::refreshOffsets()
{
/* Now calculate the offsets. */
for (int i = 0, j = 0; i < available.count(); i++)
{
if (available.at(i) == AVAILABLE)
{
rowOffsets[j] = i - j;
j++;
}
}
}
bool EditableSqlModel::removeRows ( int row, int count, const QModelIndex & parent)
{
beginRemoveRows(parent, row, row + count - 1);
if (row == rowCount() - 1)
{
/* Don't let the new addition row be deleted. */
endRemoveRows();
beginInsertRows(parent, row, row);
endInsertRows();
return false;
}
QSqlQuery query;
query.prepare("DELETE FROM T1 WHERE id=:id");
for (int i = row + count - 1; i >= row; i--)
{
query.bindValue(":id", data(index(i, 0)).toInt());
query.exec();
}
/* Get real rows deleted and mark as deleted. */
for (int i = row; i < row + count; i++)
{
int rowreal = i + rowOffsets.at(i);
available[rowreal] = UNAVAILABLE;
hash.remove(QString("%1_0").arg(rowreal));
hash.remove(QString("%1_1").arg(rowreal));
hash.remove(QString("%1_2").arg(rowreal));
}
refreshOffsets();
rows -= count;
endRemoveRows();
return true;
}
void MainWindow::deleteEntry()
{
QModelIndexList idxs = tv.selectionModel()->selectedRows();
/* Must do this one at a time or sort and go backwards. */
while (!idxs.isEmpty())
{
tv.model()->removeRow(idxs.at(0).row(), idxs.at(0).parent());
idxs = tv.selectionModel()->selectedRows();
}
}
MainWindow::MainWindow(QWidget *parent) :
QMainWindow(parent), deleteAction("Delete", this), tv(this)
{
model.setQuery("SELECT id, First, Last FROM T1");
tv.setModel(&model);
tv.setColumnHidden(0, true); /* Hide primary key */
tv.setSortingEnabled(true);
tv.addAction(&deleteAction);
tv.setContextMenuPolicy(Qt::ActionsContextMenu);
connect(&deleteAction, SIGNAL(triggered()), this, SLOT(deleteEntry()));
setCentralWidget(&tv);
tv.show();
}
void setupDb(QSqlDatabase & db)
{
db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(":memory:");
db.open();
QString sql = QString("CREATE TABLE T1(id INTEGER PRIMARY KEY AUTOINCREMENT, First, Last)");
QSqlQuery query(db);
query.exec(sql);
db.transaction();
query.prepare("INSERT INTO T1 (First, Last) "
"VALUES (:2, :3)");
for (int i = 0; i < 5; i++)
{
query.bindValue(":2", QString("Bart %1").arg(i));
query.bindValue(":3", "Simpson");
query.exec();
}
db.commit();
}
int main(int argc, char *argv[])
{
QApplication a(argc, argv);
QSqlDatabase db;
setupDb(db);
MainWindow win(0);
win.show();
return a.exec();
}
To copy to clipboard, switch view to plain text mode
Bookmarks