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

4 files changed, 261 insertions, 3 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);
+}
diff --git a/mappingtablemodel.h b/mappingtablemodel.h
new file mode 100644
index 0000000..f624bff
--- /dev/null
+++ b/mappingtablemodel.h
@@ -0,0 +1,62 @@
+/*
+  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.
+*/
+
+#ifndef MAPPINGTABLEMODEL_H
+#define MAPPINGTABLEMODEL_H
+
+#include <QSqlDatabase>
+#include <QList>
+
+#include "smtreemodel.h"
+
+class QString;
+class QSqlQuery;
+class QStringList;
+
+class MappingTableModel : public SmTreeModel{
+	Q_OBJECT
+	public:
+		enum CustomRoles { ItemNameRole = Qt::UserRole + 1, ItemIdRole = Qt::UserRole + 2 };
+		enum Fields { ItemName = 0, ItemId = 1 };
+		explicit MappingTableModel(QStringList &headers, const QString &table, QObject *parent = 0);
+		virtual ~MappingTableModel();
+
+		//data + flags
+		virtual Qt::ItemFlags flags(const QModelIndex &index) const;
+		virtual QVariant data(const QModelIndex &index, int role) const;
+		virtual bool setData(const QModelIndex &index, const QVariant &value, int role);
+
+		//add and remove items
+		bool addItem(const QVariant &name);
+		bool removeItem(const QModelIndex &idx);
+
+		//mappings
+		bool addMapping(int seriesId, const QList<int> &itemIds);
+		QList<QVariant> mappings(int seriesId);
+
+	signals:
+		void needResort();
+
+	private:
+		void populate();
+		QSqlDatabase mDb;
+		QString mTable;
+		QString mMappingTable;
+		QString mIdColumnName;
+		QString mNameColumnName;
+		QString mSequenceName;
+		QStringList mItemNames;
+
+		//queries
+		QSqlQuery *mUpdateItemNameQuery;
+		QSqlQuery *mInsertItemQuery;
+		QSqlQuery *mDeleteItemQuery;
+		QSqlQuery *mAddMappingQuery;
+		QSqlQuery *mMappingQuery;
+};
+
+#endif // MAPPINGTABLEMODEL_H
diff --git a/shemov.pro b/shemov.pro
index 491142b..60bc419 100644
--- a/shemov.pro
+++ b/shemov.pro
@@ -53,7 +53,8 @@ SOURCES = main.cpp \
     seriestreewidget.cpp \
     seriestreemodel.cpp \
     filestreemodel.cpp \
-    filestreewidget.cpp
+    filestreewidget.cpp \
+    mappingtablemodel.cpp
 HEADERS = listitem.h \
     listmodel.h \
     movieitem.h \
@@ -102,6 +103,7 @@ HEADERS = listitem.h \
     seriestreewidget.h \
     seriestreemodel.h \
     filestreemodel.h \
-    filestreewidget.h
+    filestreewidget.h \
+    mappingtablemodel.h
 LIBS += -lmagic
 RESOURCES = shemov.qrc
diff --git a/smmodelsingleton.cpp b/smmodelsingleton.cpp
index 08cbbba..2ee5623 100644
--- a/smmodelsingleton.cpp
+++ b/smmodelsingleton.cpp
@@ -12,6 +12,7 @@
 #include "smmodelsingleton.h"
 #include "seriestreemodel.h"
 #include "filestreemodel.h"
+#include "mappingtablemodel.h"
 
 SmModelSingleton *SmModelSingleton::mInstance = 0;
 
@@ -50,6 +51,18 @@ QAbstractItemModel *SmModelSingleton::model(const QString &which){
 			FilesTreeModel *model = new FilesTreeModel(headers);
 			mModels.insert(which, model);
 		}
+	}else if(which == "ActorsModel"){
+		if(!mModels.contains(which)){
+			QStringList headers = QStringList() << tr("Actor") << tr("Id");
+			MappingTableModel *model = new MappingTableModel(headers, "actors");
+			mModels.insert(which, model);
+		}
+	}else if(which == "GenresModel"){
+		if(!mModels.contains(which)){
+			QStringList headers = QStringList() << tr("Genre") << tr("Id");
+			MappingTableModel *model = new MappingTableModel(headers, "genres");
+			mModels.insert(which, model);
+		}
 	}
-	return mModels.contains(which) ? mModels.value(which ) : 0;
+	return mModels.contains(which) ? mModels.value(which) : 0;
 }