Added database maintenance code

Implemented a function in MappingTableModel to remove all actors/genres
with no references in the according mapping table. Available through the
"File" menu.

1 files changed, 44 insertions, 0 deletions

diff --git a/mappingtablemodel.cpp b/mappingtablemodel.cpp
index 65221e4..47e3136 100644
--- a/mappingtablemodel.cpp
+++ b/mappingtablemodel.cpp
@@ -39,6 +39,12 @@ MappingTableModel::MappingTableModel(QStringList &headers, const QString &table,
 	QString mappingQuery = QString("SELECT %1 FROM %2, %3 WHERE %2.%4 = %3.%4 AND %3.iseriesparts_id = :id").arg(mNameColumnName).arg(mTable).arg(mMappingTable).arg(mIdColumnName);
 	mMappingQuery = new QSqlQuery(mDb);
 	mMappingQuery->prepare(mappingQuery);
+	QString allItemsQuery = QString("SELECT %1, %2 FROM %3").arg(mIdColumnName).arg(mNameColumnName).arg(mTable);
+	mAllItemsQuery = new QSqlQuery(mDb);
+	mAllItemsQuery->prepare(allItemsQuery);
+	QString itemCountQuery = QString("SELECT COUNT(*) FROM %1 WHERE %2 = :id").arg(mMappingTable).arg(mIdColumnName);
+	mItemCountQuery = new QSqlQuery(mDb);
+	mItemCountQuery->prepare(itemCountQuery);
 
 	//get data
 	populate();
@@ -51,6 +57,8 @@ MappingTableModel::~MappingTableModel(){
 	delete mAddMappingQuery;
 	delete mRemoveMappingQuery;
 	delete mMappingQuery;
+	delete mAllItemsQuery;
+	delete mItemCountQuery;
 	mDb = QSqlDatabase();
 }
 
@@ -194,9 +202,45 @@ QList<QVariant> MappingTableModel::mappings(int seriesId){
 	return retval;
 }
 
+QHash<QString, int> MappingTableModel::zeroUsers(){
+	QHash<int, QString> items;
+	mAllItemsQuery->exec();
+	while(mAllItemsQuery->next()){
+		items.insert(mAllItemsQuery->value(0).toInt(), mAllItemsQuery->value(1).toString());
+	}
+	QHash<QString, int> retval;
+	if(!items.isEmpty()){
+		QList<int> ids = items.keys();
+		foreach(int id, ids){
+			mItemCountQuery->bindValue(":id", id);
+			if(mItemCountQuery->exec()){
+				mItemCountQuery->first();
+				int count = mItemCountQuery->value(0).toInt();
+				if(count == 0){
+					QString itemName = items.value(id);
+					retval.insert(itemName, id);
+				}
+			}
+		}
+	}
+	return retval;
+}
+
+void MappingTableModel::deleteItems(const QList<int> &ids){
+	if(ids.isEmpty()){
+		return;
+	}
+	foreach(int id, ids){
+		mDeleteItemQuery->bindValue(":id", id);
+		mDeleteItemQuery->exec();
+	}
+	populate();
+}
+
 void MappingTableModel::populate(){
 	QString query = QString("SELECT %1, %2 FROM %3 ORDER BY %2").arg(mIdColumnName).arg(mNameColumnName).arg(mTable);
 	QList<QVariant> rootData;
+	mItemNames.clear();
 	rootData << QString(tr("%1 name")).arg(mTable) << tr("Id");
 	SmTreeItem *root = new SmTreeItem(rootData);
 	QSqlQuery dataQuery(query, mDb);