New model: MappingTableModel

Implemented new Model for Mapping tables. This model is intended to
replace the old ListModel and Singleton. MappingTableModel is derived
from SmTreeModel and can be accessed using SmModelSingleton.

It's quite dynamic building its queries, and has yet to be tested.
Hopefully I got it right the first time :)

1 files changed, 181 insertions, 0 deletions

diff --git a/mappingtablemodel.cpp b/mappingtablemodel.cpp
new file mode 100644
index 0000000..84c242a
--- /dev/null
+++ b/mappingtablemodel.cpp
@@ -0,0 +1,181 @@
+/*
+  This program is free software; you can redistribute it and/or
+  modify it under the terms of the GNU General Public License
+  as published by the Free Software Foundation; either version
+  2 of the License, or (at your option) any later version.
+*/
+
+#include <QSqlQuery>
+
+#include "mappingtablemodel.h"
+#include "smtreeitem.h"
+
+MappingTableModel::MappingTableModel(QStringList &headers, const QString &table, QObject *parent) : SmTreeModel(headers, parent), mTable(table){
+	//database setup
+	mDb = QSqlDatabase::database("treedb");
+	QString tmpTable = mTable;
+	tmpTable.chop(1);
+	mMappingTable = QString("seriesparts_%1map").arg(tmpTable);
+	mIdColumnName = QString("i%1_id").arg(table);
+	mNameColumnName = QString("t%1name").arg(tmpTable);
+	mSequenceName = QString("%1_i%1_id__seq").arg(mTable);
+
+	//queries
+	QString updateNameQuery = QString("UPDATE %1 set %2 = :name WHERE %3 = :id").arg(mTable).arg(mNameColumnName).arg(mIdColumnName);
+	mUpdateItemNameQuery = new QSqlQuery(mDb);
+	mUpdateItemNameQuery->prepare(updateNameQuery);
+	QString insertItemQuery = QString("INSERT INTO %1 (%2) VALUES (:name)").arg(mTable).arg(mNameColumnName);
+	mInsertItemQuery = new QSqlQuery(mDb);
+	mInsertItemQuery->prepare(insertItemQuery);
+	QString deleteItemQuery = QString("DELETE FROM %1 WHERE %2 = :id").arg(mTable).arg(mIdColumnName);
+	mDeleteItemQuery = new QSqlQuery(mDb);
+	mDeleteItemQuery->prepare(deleteItemQuery);
+	QString addMappingQuery = QString("INSERT INTO %1 (iseriesparts_id, %2) VALUES (:id1, :id2)").arg(mMappingTable).arg(mIdColumnName);
+	mAddMappingQuery = new QSqlQuery(mDb);
+	mAddMappingQuery->prepare(addMappingQuery);
+	QString mappingQuery = QString("SELECT %1 FROM %2, %3 WHERE %1.%4 = %3.%4 AND %3.iseriesparts_id = :id").arg(mNameColumnName).arg(mTable).arg(mMappingTable).arg(mIdColumnName);
+	mMappingQuery = new QSqlQuery(mDb);
+	mMappingQuery->prepare(mappingQuery);
+
+	//get data
+	populate();
+}
+
+MappingTableModel::~MappingTableModel(){
+	delete mUpdateItemNameQuery;
+	delete mInsertItemQuery;
+	delete mDeleteItemQuery;
+	delete mAddMappingQuery;
+	delete mMappingQuery;
+	mDb = QSqlDatabase();
+}
+
+Qt::ItemFlags MappingTableModel::flags(const QModelIndex &index) const{
+	if(!index.isValid()){
+		return 0;
+	}
+	Qt::ItemFlags retval = Qt::ItemIsEnabled | Qt::ItemIsSelectable;
+	if(index.column() == ItemName){
+		return retval | Qt::ItemIsEditable;
+	}
+	return retval;
+}
+
+QVariant MappingTableModel::data(const QModelIndex &index, int role) const{
+	if(!index.isValid()){
+		return QVariant();
+	}
+
+	SmTreeItem *item = static_cast<SmTreeItem*>(index.internalPointer());
+
+	if(role == Qt::DisplayRole || role == Qt::EditRole){
+		return item->data(index.column());
+	}
+	if(role == ItemNameRole){
+		return item->data(ItemName);
+	}
+	if(role == ItemIdRole){
+		return item->data(ItemId);
+	}
+	return QVariant();
+}
+
+bool MappingTableModel::setData(const QModelIndex &index, const QVariant &value, int role){
+	if(role != Qt::EditRole || !index.isValid()){
+		return false;
+	}
+	if(index.column() != ItemName){
+		return false;
+	}
+	SmTreeItem *item = static_cast<SmTreeItem*>(index.internalPointer());
+	QString newName = value.toString().toLower().trimmed();
+	mUpdateItemNameQuery->bindValue(":name", newName);
+	mUpdateItemNameQuery->bindValue(":id", item->data(ItemId));
+	if(mUpdateItemNameQuery->exec()){
+		item->setData(ItemName, newName);
+		return true;
+	}
+	return false;
+}
+
+bool MappingTableModel::addItem(const QVariant &name){
+	QString newValue = name.toString().toLower().trimmed();
+	if(mItemNames.contains(newValue)){
+		return false;
+	}
+	mDb.transaction();
+	mInsertItemQuery->bindValue(":name", newValue);
+	if(mInsertItemQuery->exec()){
+		QString sequenceQuery = QString("SELECT currval('%1')").arg(mSequenceName);
+		QSqlQuery lastId(sequenceQuery, mDb);
+		if(lastId.next()){
+			QVariant newId = lastId.value(0);
+			QList<QVariant> data;
+			data << newValue << newId;
+			addRow(data, QModelIndex());
+			mItemNames << newValue;
+			emit needResort();
+			return true;
+		}
+	}
+	mDb.rollback();
+	return false;
+}
+
+bool MappingTableModel::removeItem(const QModelIndex &idx){
+	if(!idx.isValid()){
+		return false;
+	}
+	QString name = idx.data(ItemNameRole).toString();
+	if(!mItemNames.contains(name)){
+		return false;
+	}
+	mDeleteItemQuery->bindValue(":id", idx.data(ItemIdRole));
+	if(mDeleteItemQuery->exec()){
+		removeRows(idx.row(), 1, idx.parent());
+		return true;
+	}
+	return false;
+}
+
+bool MappingTableModel::addMapping(int seriesId, const QList<int> &itemIds){
+	if(itemIds.isEmpty()){
+		return false;
+	}
+	mDb.transaction();
+	foreach(int id, itemIds){
+		mAddMappingQuery->bindValue(":id1", seriesId);
+		mAddMappingQuery->bindValue(":id2", id);
+		if(!mAddMappingQuery->exec()){
+			mDb.rollback();
+			return false;
+		}
+	}
+	return true;
+}
+
+QList<QVariant> MappingTableModel::mappings(int seriesId){
+	QList<QVariant> retval;
+	mMappingQuery->bindValue(":id", seriesId);
+	if(mMappingQuery->exec()){
+		while(mMappingQuery->next()){
+			retval << mMappingQuery->value(0);
+		}
+	}
+	return retval;
+}
+
+void MappingTableModel::populate(){
+	QString query = QString("SELECT %1, %2 FROM %3 ORDER BY %2").arg(mIdColumnName).arg(mNameColumnName).arg(mTable);
+	SmTreeItem *root = new SmTreeItem(2);
+	QSqlQuery dataQuery(query, mDb);
+	while(dataQuery.next()){
+		QList<QVariant> data;
+		data << dataQuery.value(1); //name
+		data << dataQuery.value(0); //id
+		SmTreeItem *item = new SmTreeItem(data, root);
+		root->appendChild(item);
+		mItemNames << dataQuery.value(1).toString();
+	}
+	setRoot(root);
+}