From 6d3b28f9b12964b161b5a7ac0038435cfdd6e27b Mon Sep 17 00:00:00 2001 From: Arno Date: Sat, 31 Jul 2010 11:05:59 +0200 Subject: 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. --- archivetreeview.cpp | 29 +++++++++++++++++++++++++++++ archivetreeview.h | 1 + mappingtablemodel.cpp | 44 ++++++++++++++++++++++++++++++++++++++++++++ mappingtablemodel.h | 6 ++++++ seriestreemodel.cpp | 1 + shemov.cpp | 18 ++++++++++++++++-- shemov.h | 2 ++ 7 files changed, 99 insertions(+), 2 deletions(-) diff --git a/archivetreeview.cpp b/archivetreeview.cpp index 1ae3cc8..1d66fbe 100644 --- a/archivetreeview.cpp +++ b/archivetreeview.cpp @@ -16,6 +16,7 @@ #include #include #include +#include #include "archivetreeview.h" #include "smglobals.h" @@ -99,6 +100,34 @@ void ArchiveTreeView::setFileViewMode(int mode){ constructWindowTitle(); } +void ArchiveTreeView::cleanDatabase(const QString &table){ + MappingTableModel *model = 0; + if(table == "genres"){ + model = mGenresModel; + }else if(table == "actors"){ + model = mActorsModel; + } + Q_ASSERT(model); + QHash zeroUsers = model->zeroUsers(); + if(zeroUsers.isEmpty()){ + QMessageBox::information(this, tr("Message"), tr("Nothing to clean up!")); + return; + } + QString message = QString(tr("

Really delete these items from %1?

")).arg(table); + message.append("

    "); + QStringList userNames = zeroUsers.keys(); + qSort(userNames); + foreach(QString s, userNames){ + message.append(QString("
  • %1
    • ").arg(s)); + } + message.append("
"); + int answer = QMessageBox::question(this, tr("Question"), message, QMessageBox::Yes | QMessageBox::No); + if(answer == QMessageBox::Yes){ + QList ids = zeroUsers.values(); + model->deleteItems(ids); + } +} + void ArchiveTreeView::currentChanged(const QItemSelection &selected, const QItemSelection &deselected){ Q_UNUSED(selected); Q_UNUSED(deselected); diff --git a/archivetreeview.h b/archivetreeview.h index 24ef2f8..9ea2604 100644 --- a/archivetreeview.h +++ b/archivetreeview.h @@ -35,6 +35,7 @@ class ArchiveTreeView : public QWidget public slots: void setFileViewMode(int mode); + void cleanDatabase(const QString &table); private slots: void currentChanged(const QItemSelection &selected, const QItemSelection &deselected); 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 MappingTableModel::mappings(int seriesId){ return retval; } +QHash MappingTableModel::zeroUsers(){ + QHash items; + mAllItemsQuery->exec(); + while(mAllItemsQuery->next()){ + items.insert(mAllItemsQuery->value(0).toInt(), mAllItemsQuery->value(1).toString()); + } + QHash retval; + if(!items.isEmpty()){ + QList 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 &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 rootData; + mItemNames.clear(); rootData << QString(tr("%1 name")).arg(mTable) << tr("Id"); SmTreeItem *root = new SmTreeItem(rootData); QSqlQuery dataQuery(query, mDb); diff --git a/mappingtablemodel.h b/mappingtablemodel.h index ae64600..9d00f72 100644 --- a/mappingtablemodel.h +++ b/mappingtablemodel.h @@ -41,6 +41,10 @@ class MappingTableModel : public SmTreeModel{ bool removeMapping(int seriesId, int itemId); QList mappings(int seriesId); + //database maintenance + QHash zeroUsers(); + void deleteItems(const QList &ids); + signals: void needResort(); @@ -61,6 +65,8 @@ class MappingTableModel : public SmTreeModel{ QSqlQuery *mAddMappingQuery; QSqlQuery *mRemoveMappingQuery; QSqlQuery *mMappingQuery; + QSqlQuery *mAllItemsQuery; + QSqlQuery *mItemCountQuery; }; #endif // MAPPINGTABLEMODEL_H diff --git a/seriestreemodel.cpp b/seriestreemodel.cpp index fb993bb..e25e759 100644 --- a/seriestreemodel.cpp +++ b/seriestreemodel.cpp @@ -324,6 +324,7 @@ bool SeriesTreeModel::addSeriesPart(int seriesPart, const QModelIndex &parent){ return false; } + void SeriesTreeModel::populate(){ QSqlQuery seriesQuery = QSqlQuery("SELECT iseries_id, tseries_name FROM series ORDER BY tseries_name", mDb); SmTreeItem *rootItem = new SmTreeItem(5); diff --git a/shemov.cpp b/shemov.cpp index 58f2658..1c180b7 100644 --- a/shemov.cpp +++ b/shemov.cpp @@ -253,6 +253,18 @@ void SheMov::createActions(){ //File menu mPlaySelectedFSA = new QAction(tr("Play selected movies..."), this); connect(mPlaySelectedFSA, SIGNAL(triggered()), mFSWidget, SLOT(playSelected())); + mNewMovieWizardA = new QAction(tr("Archive movie..."), this); + connect(mNewMovieWizardA, SIGNAL(triggered()), this, SLOT(newMovieWizard())); + mCleanupMapper = new QSignalMapper(this); + mCleanupGroup = new QActionGroup(this); + QAction *cleanup1 = new QAction(tr("Cleanup actors..."), this); + connect(cleanup1, SIGNAL(triggered()), mCleanupMapper, SLOT(map())); + mCleanupMapper->setMapping(cleanup1, "actors"); + mCleanupGroup->addAction(cleanup1); + QAction *cleanup2 = new QAction(tr("Cleanup genres..."), this); + connect(cleanup2, SIGNAL(triggered()), mCleanupMapper, SLOT(map())); + mCleanupMapper->setMapping(cleanup2, "genres"); + mCleanupGroup->addAction(cleanup2); mQuitA = new QAction(tr("Quit"), this); mQuitA->setShortcut(tr("CTRL+q")); @@ -329,8 +341,6 @@ void SheMov::createActions(){ connect(mCollapseAllSeriesA, SIGNAL(triggered()), mATree->seriesWidget()->seriesTree(), SLOT(collapseAll())); mExpandCurrentA = new QAction(tr("Expand"), this); connect(mExpandCurrentA, SIGNAL(triggered()), mATree->seriesWidget(), SLOT(expandCurrent())); - mNewMovieWizardA = new QAction(tr("Archive movie..."), this); - connect(mNewMovieWizardA, SIGNAL(triggered()), this, SLOT(newMovieWizard())); mAddCoverA = new QAction(tr("Add cover..."), this); connect(mAddCoverA, SIGNAL(triggered()), mATree->seriesWidget(), SLOT(addCover())); @@ -369,6 +379,7 @@ void SheMov::createActions(){ mOpenWithMapperAV = new QSignalMapper(this); connect(mOpenWithMapperFS, SIGNAL(mapped(QString)), mFSWidget, SLOT(playSelected(QString))); connect(mOpenWithMapperAV, SIGNAL(mapped(QString)), mATree, SLOT(playSelected(QString))); + connect(mCleanupMapper, SIGNAL(mapped(QString)), mATree, SLOT(cleanDatabase(QString))); connect(viewMapper, SIGNAL(mapped(int)), mATree, SLOT(setFileViewMode(int))); } @@ -379,6 +390,9 @@ void SheMov::createMenus(){ fileMenu->addAction(mPlaySelectedFSA); fileMenu->addSeparator(); fileMenu->addAction(mNewMovieWizardA); + QMenu *cleanupMenu = new QMenu(tr("Clean database"), this); + cleanupMenu->addActions(mCleanupGroup->actions()); + fileMenu->addMenu(cleanupMenu); fileMenu->addSeparator(); fileMenu->addAction(mQuitA); menuBar()->addMenu(fileMenu); diff --git a/shemov.h b/shemov.h index e7fac94..2b90b25 100644 --- a/shemov.h +++ b/shemov.h @@ -107,11 +107,13 @@ class SheMov : public QMainWindow { QActionGroup *mOpenWithGroupFS; QActionGroup *mOpenWithGroupAV; + QActionGroup *mCleanupGroup; //EndActions QSignalMapper *mRenameMapper; QSignalMapper *mOpenWithMapperFS; QSignalMapper *mOpenWithMapperAV; + QSignalMapper *mCleanupMapper; QMenu *mEditFSMenu; QMenu *mOpenWithMenuFS; -- cgit v1.2.3-70-g09d2