From: <bar...@us...> - 2009-03-21 23:59:48
|
Revision: 8578 http://bacula.svn.sourceforge.net/bacula/?rev=8578&view=rev Author: bartleyd2 Date: 2009-03-21 23:59:44 +0000 (Sat, 21 Mar 2009) Log Message: ----------- Speed up the queries on storage and medialist like with clients and filesets. Use only one query to retreive data instead of one query per pool in medialist and one query per storage. There is still the one query per changer. Modified Paths: -------------- trunk/bacula/src/qt-console/medialist/medialist.cpp trunk/bacula/src/qt-console/storage/storage.cpp Modified: trunk/bacula/src/qt-console/medialist/medialist.cpp =================================================================== --- trunk/bacula/src/qt-console/medialist/medialist.cpp 2009-03-21 21:10:29 UTC (rev 8577) +++ trunk/bacula/src/qt-console/medialist/medialist.cpp 2009-03-21 23:59:44 UTC (rev 8578) @@ -104,17 +104,22 @@ QString query; QTreeWidgetItem *pooltreeitem; + + /* Comma separated list of pools first */ + bool first = true; + QString pool_comsep(""); foreach (QString pool_listItem, m_console->pool_list) { - pooltreeitem = new QTreeWidgetItem(m_topItem); - pooltreeitem->setText(0, pool_listItem); - pooltreeitem->setData(0, Qt::UserRole, 1); - if(settings.contains(pool_listItem)) { - pooltreeitem->setExpanded(settings.value(pool_listItem).toBool()); - } else { - pooltreeitem->setExpanded(true); + if (first) { + pool_comsep += "'" + pool_listItem + "'"; + first = false; } - - query = "SELECT Media.VolumeName AS Media, " + else + pool_comsep += ",'" + pool_listItem + "'"; + } + /* Now use pool_comsep list to perform just one query */ + if (pool_comsep != "") { + query = "SELECT Pool.Name AS pul," + " Media.VolumeName AS Media, " " Media.MediaId AS Id, Media.VolStatus AS VolStatus," " Media.Enabled AS Enabled, Media.VolBytes AS Bytes," " Media.VolFiles AS FileCount, Media.VolJobs AS JobCount," @@ -127,59 +132,74 @@ " FROM Media" " JOIN Pool ON (Media.PoolId=Pool.PoolId)" " LEFT OUTER JOIN Pool AS Pol ON (Media.RecyclePoolId=Pol.PoolId)" - " WHERE"; - query += " Pool.Name='" + pool_listItem + "'"; - query += " ORDER BY Media"; - + " WHERE "; + query += " Pool.Name IN (" + pool_comsep + ")"; + query += " ORDER BY Pool.Name, Media"; + if (mainWin->m_sqlDebug) { Pmsg1(000, "MediaList query cmd : %s\n",query.toUtf8().data()); } QStringList results; + int counter = 0; if (m_console->sql_cmd(query, results)) { QStringList fieldlist; + QString prev_pool(""); + QString this_pool(""); /* Iterate through the lines of results. */ foreach (QString resultline, results) { fieldlist = resultline.split("\t"); + this_pool = fieldlist.takeFirst(); + if (prev_pool != this_pool) { + prev_pool = this_pool; + pooltreeitem = new QTreeWidgetItem(m_topItem); + pooltreeitem->setText(0, this_pool); + pooltreeitem->setData(0, Qt::UserRole, 1); + } + if(settings.contains(this_pool)) { + pooltreeitem->setExpanded(settings.value(this_pool).toBool()); + } else { + pooltreeitem->setExpanded(true); + } - if (fieldlist.size() < 18) - continue; // some fields missing, ignore row + if (fieldlist.size() < 18) + continue; // some fields missing, ignore row int index = 0; - TreeItemFormatter mediaitem(*pooltreeitem, 2); + TreeItemFormatter mediaitem(*pooltreeitem, 2); /* Iterate through fields in the record */ - QStringListIterator fld(fieldlist); + QStringListIterator fld(fieldlist); - /* volname */ + /* volname */ mediaitem.setTextFld(index++, fld.next()); - /* id */ + /* id */ mediaitem.setNumericFld(index++, fld.next()); - /* status */ + /* status */ mediaitem.setVolStatusFld(index++, fld.next()); - /* enabled */ - mediaitem.setBoolFld(index++, fld.next()); + /* enabled */ + mediaitem.setBoolFld(index++, fld.next()); /* bytes */ - mediaitem.setBytesFld(index++, fld.next()); + mediaitem.setBytesFld(index++, fld.next()); - /* files */ + /* files */ mediaitem.setNumericFld(index++, fld.next()); - /* jobs */ + /* jobs */ mediaitem.setNumericFld(index++, fld.next()); - /* retention */ - mediaitem.setDurationFld(index++, fld.next()); + /* retention */ + mediaitem.setDurationFld(index++, fld.next()); - /* media type */ + /* media type */ mediaitem.setTextFld(index++, fld.next()); - /* inchanger + slot */ - int inchanger = fld.next().toInt(); + /* inchanger + slot */ + int inchanger = fld.next().toInt(); if (inchanger) { mediaitem.setNumericFld(index++, fld.next()); } @@ -187,30 +207,31 @@ /* volume not in changer, show blank slot */ mediaitem.setNumericFld(index++, ""); fld.next(); - } + } - /* use duration */ - mediaitem.setDurationFld(index++, fld.next()); + /* use duration */ + mediaitem.setDurationFld(index++, fld.next()); - /* max jobs */ + /* max jobs */ mediaitem.setNumericFld(index++, fld.next()); - /* max files */ + /* max files */ mediaitem.setNumericFld(index++, fld.next()); /* max bytes */ - mediaitem.setBytesFld(index++, fld.next()); + mediaitem.setBytesFld(index++, fld.next()); - /* recycle */ - mediaitem.setBoolFld(index++, fld.next()); + /* recycle */ + mediaitem.setBoolFld(index++, fld.next()); - /* recycle pool */ + /* recycle pool */ mediaitem.setTextFld(index++, fld.next()); - /* last written */ + /* last written */ mediaitem.setTextFld(index++, fld.next()); } /* foreach resultline */ + counter += 1; } /* if results from query */ } /* foreach pool_listItem */ settings.endGroup(); Modified: trunk/bacula/src/qt-console/storage/storage.cpp =================================================================== --- trunk/bacula/src/qt-console/storage/storage.cpp 2009-03-21 21:10:29 UTC (rev 8577) +++ trunk/bacula/src/qt-console/storage/storage.cpp 2009-03-21 23:59:44 UTC (rev 8578) @@ -78,7 +78,7 @@ writeExpandedSettings(); m_populated = true; - Freeze frz(*mp_treeWidget); /* disable updating*/ + Freeze frz(*mp_treeWidget); /* disable updating */ m_checkcurwidget = false; mp_treeWidget->clear(); @@ -99,20 +99,27 @@ QSettings settings(m_console->m_dir->name(), "bat"); settings.beginGroup("StorageTreeExpanded"); - foreach(QString storageName, m_console->storage_list){ - TreeItemFormatter storageItem(*m_topItem, 1); - storageItem.setTextFld(0, storageName); - if(settings.contains(storageName)) { - storageItem.widget()->setExpanded(settings.value(storageName).toBool()); - } else { - storageItem.widget()->setExpanded(true); + bool first = true; + QString storage_comsep(""); + QString storageName; + foreach(storageName, m_console->storage_list){ + if (first) { + storage_comsep += "'" + storageName + "'"; + first = false; } + else + storage_comsep += ",'" + storageName + "'"; + } + if (storage_comsep != "") { /* Set up query QString and header QStringList */ - QString query("SELECT StorageId AS ID, AutoChanger AS Changer" - " FROM Storage WHERE"); - query += " Name='" + storageName + "'" - " ORDER BY Name"; + QString query("SELECT" + " Name AS StorageName," + " StorageId AS ID, AutoChanger AS Changer" + " FROM Storage " + " WHERE StorageId IN (SELECT MAX(StorageId) FROM Storage WHERE"); + query += " Name IN (" + storage_comsep + ")"; + query += " GROUP BY Name) ORDER BY Name"; QStringList results; /* This could be a log item */ @@ -120,25 +127,31 @@ Pmsg1(000, "Storage query cmd : %s\n",query.toUtf8().data()); } if (m_console->sql_cmd(query, results)) { - int resultCount = results.count(); - if (resultCount == 1){ - QString resultline; - QString field; - QStringList fieldlist; - /* there will only be one of these */ - foreach (resultline, results) { - fieldlist = resultline.split("\t"); - int index = 1; - QStringListIterator fld(fieldlist); + + QStringList fieldlist; + int cnter = 1; + foreach (QString resultline, results) { + fieldlist = resultline.split("\t"); + storageName = fieldlist.takeFirst(); + TreeItemFormatter storageItem(*m_topItem, 1); + storageItem.setTextFld(0, storageName); + if(settings.contains(storageName)) + storageItem.widget()->setExpanded(settings.value(storageName).toBool()); + else + storageItem.widget()->setExpanded(true); + + int index = 1; + QStringListIterator fld(fieldlist); - /* storage id */ - storageItem.setNumericFld(index++, fld.next() ); - - /* changer */ - storageItem.setBoolFld(index++, fld.next() ); - - mediaList(storageItem.widget(), fieldlist.first()); - } + /* storage id */ + storageItem.setNumericFld(index++, fld.next() ); + + /* changer */ + QString changer = fld.next(); + storageItem.setBoolFld(index++, changer); + + if (changer == "1") + mediaList(storageItem.widget(), fieldlist.first()); } } } @@ -147,16 +160,20 @@ mp_treeWidget->resizeColumnToContents(cnter); } } + +/* + * For autochangers A query to show the tapes in the changer. + */ void Storage::mediaList(QTreeWidgetItem *parent, const QString &storageID) { QString query("SELECT Media.VolumeName AS Media, Media.Slot AS Slot," " Media.VolStatus AS VolStatus, Media.Enabled AS Enabled," - " Pool.Name AS MediaPool, Media.MediaType AS MediaType" + " Pool.Name AS MediaPool, Media.MediaType AS MediaType" " From Media" - " JOIN Pool ON (Media.PoolId=Pool.PoolId)" - " WHERE Media.StorageId='" + storageID + "'" - " AND Media.InChanger<>0" - " ORDER BY Media.Slot"); + " JOIN Pool ON (Media.PoolId=Pool.PoolId)" + " WHERE Media.StorageId='" + storageID + "'" + " AND Media.InChanger<>0" + " ORDER BY Media.Slot"); QStringList results; /* This could be a log item */ @@ -170,25 +187,25 @@ foreach (resultline, results) { fieldlist = resultline.split("\t"); - if (fieldlist.size() < 6) - continue; + if (fieldlist.size() < 6) + continue; /* Iterate through fields in the record */ QStringListIterator fld(fieldlist); int index = 0; - TreeItemFormatter fmt(*parent, 2); + TreeItemFormatter fmt(*parent, 2); /* volname */ fmt.setTextFld(index++, fld.next()); - /* skip the next two columns, unused by media */ - index += 2; + /* skip the next two columns, unused by media */ + index += 2; - /* slot */ - fmt.setNumericFld(index++, fld.next()); + /* slot */ + fmt.setNumericFld(index++, fld.next()); - /* status */ - fmt.setVolStatusFld(index++, fld.next()); + /* status */ + fmt.setVolStatusFld(index++, fld.next()); /* enabled */ fmt.setBoolFld(index++, fld.next()); This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |