| 1 | #!/usr/bin/env python
|
|---|
| 2 | # -*- coding: utf-8 -*-
|
|---|
| 3 | # $Id: partial-db-dump.py 79087 2019-06-11 11:58:28Z vboxsync $
|
|---|
| 4 | # pylint: disable=line-too-long
|
|---|
| 5 |
|
|---|
| 6 | """
|
|---|
| 7 | Utility for dumping the last X days of data.
|
|---|
| 8 | """
|
|---|
| 9 |
|
|---|
| 10 | __copyright__ = \
|
|---|
| 11 | """
|
|---|
| 12 | Copyright (C) 2012-2019 Oracle Corporation
|
|---|
| 13 |
|
|---|
| 14 | This file is part of VirtualBox Open Source Edition (OSE), as
|
|---|
| 15 | available from http://www.virtualbox.org. This file is free software;
|
|---|
| 16 | you can redistribute it and/or modify it under the terms of the GNU
|
|---|
| 17 | General Public License (GPL) as published by the Free Software
|
|---|
| 18 | Foundation, in version 2 as it comes in the "COPYING" file of the
|
|---|
| 19 | VirtualBox OSE distribution. VirtualBox OSE is distributed in the
|
|---|
| 20 | hope that it will be useful, but WITHOUT ANY WARRANTY of any kind.
|
|---|
| 21 |
|
|---|
| 22 | The contents of this file may alternatively be used under the terms
|
|---|
| 23 | of the Common Development and Distribution License Version 1.0
|
|---|
| 24 | (CDDL) only, as it comes in the "COPYING.CDDL" file of the
|
|---|
| 25 | VirtualBox OSE distribution, in which case the provisions of the
|
|---|
| 26 | CDDL are applicable instead of those of the GPL.
|
|---|
| 27 |
|
|---|
| 28 | You may elect to license modified versions of this file under the
|
|---|
| 29 | terms and conditions of either the GPL or the CDDL or both.
|
|---|
| 30 | """
|
|---|
| 31 | __version__ = "$Revision: 79087 $"
|
|---|
| 32 |
|
|---|
| 33 | # Standard python imports
|
|---|
| 34 | import sys;
|
|---|
| 35 | import os;
|
|---|
| 36 | import zipfile;
|
|---|
| 37 | from optparse import OptionParser;
|
|---|
| 38 | import xml.etree.ElementTree as ET;
|
|---|
| 39 |
|
|---|
| 40 | # Add Test Manager's modules path
|
|---|
| 41 | g_ksTestManagerDir = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))));
|
|---|
| 42 | sys.path.append(g_ksTestManagerDir);
|
|---|
| 43 |
|
|---|
| 44 | # Test Manager imports
|
|---|
| 45 | from testmanager.core.db import TMDatabaseConnection;
|
|---|
| 46 | from common import utils;
|
|---|
| 47 |
|
|---|
| 48 |
|
|---|
| 49 | class PartialDbDump(object): # pylint: disable=too-few-public-methods
|
|---|
| 50 | """
|
|---|
| 51 | Dumps or loads the last X days of database data.
|
|---|
| 52 |
|
|---|
| 53 | This is a useful tool when hacking on the test manager locally. You can get
|
|---|
| 54 | a small sample from the last few days from the production test manager server
|
|---|
| 55 | without spending hours dumping, downloading, and loading the whole database
|
|---|
| 56 | (because it is gigantic).
|
|---|
| 57 |
|
|---|
| 58 | """
|
|---|
| 59 |
|
|---|
| 60 | def __init__(self):
|
|---|
| 61 | """
|
|---|
| 62 | Parse command line.
|
|---|
| 63 | """
|
|---|
| 64 |
|
|---|
| 65 | oParser = OptionParser()
|
|---|
| 66 | oParser.add_option('-q', '--quiet', dest = 'fQuiet', action = 'store_true',
|
|---|
| 67 | help = 'Quiet execution');
|
|---|
| 68 | oParser.add_option('-f', '--filename', dest = 'sFilename', metavar = '<filename>',
|
|---|
| 69 | default = 'partial-db-dump.zip', help = 'The name of the partial database zip file to write/load.');
|
|---|
| 70 |
|
|---|
| 71 | oParser.add_option('-t', '--tmp-file', dest = 'sTempFile', metavar = '<temp-file>',
|
|---|
| 72 | default = '/tmp/tm-partial-db-dump.pgtxt',
|
|---|
| 73 | help = 'Name of temporary file for duping tables. Must be absolute');
|
|---|
| 74 | oParser.add_option('--days-to-dump', dest = 'cDays', metavar = '<days>', type = 'int', default = 14,
|
|---|
| 75 | help = 'How many days to dump (counting backward from current date).');
|
|---|
| 76 | oParser.add_option('--load-dump-into-database', dest = 'fLoadDumpIntoDatabase', action = 'store_true',
|
|---|
| 77 | default = False, help = 'For loading instead of dumping.');
|
|---|
| 78 |
|
|---|
| 79 | (self.oConfig, _) = oParser.parse_args();
|
|---|
| 80 |
|
|---|
| 81 |
|
|---|
| 82 | ##
|
|---|
| 83 | # Tables dumped in full because they're either needed in full or they normally
|
|---|
| 84 | # aren't large enough to bother reducing.
|
|---|
| 85 | kasTablesToDumpInFull = [
|
|---|
| 86 | 'Users',
|
|---|
| 87 | 'BuildBlacklist',
|
|---|
| 88 | 'BuildCategories',
|
|---|
| 89 | 'BuildSources',
|
|---|
| 90 | 'FailureCategories',
|
|---|
| 91 | 'FailureReasons',
|
|---|
| 92 | 'GlobalResources',
|
|---|
| 93 | 'TestBoxStrTab',
|
|---|
| 94 | 'Testcases',
|
|---|
| 95 | 'TestcaseArgs',
|
|---|
| 96 | 'TestcaseDeps',
|
|---|
| 97 | 'TestcaseGlobalRsrcDeps',
|
|---|
| 98 | 'TestGroups',
|
|---|
| 99 | 'TestGroupMembers',
|
|---|
| 100 | 'SchedGroups',
|
|---|
| 101 | 'SchedGroupMembers', # ?
|
|---|
| 102 | 'SchedQueues',
|
|---|
| 103 | 'Builds', # ??
|
|---|
| 104 | 'VcsRevisions', # ?
|
|---|
| 105 | 'TestResultStrTab', # 36K rows, never mind complicated then.
|
|---|
| 106 | ];
|
|---|
| 107 |
|
|---|
| 108 | ##
|
|---|
| 109 | # Tables where we only dump partial info (the TestResult* tables are rather
|
|---|
| 110 | # gigantic).
|
|---|
| 111 | kasTablesToPartiallyDump = [
|
|---|
| 112 | 'TestBoxes', # 2016-05-25: ca. 641 MB
|
|---|
| 113 | 'TestSets', # 2016-05-25: ca. 525 MB
|
|---|
| 114 | 'TestResults', # 2016-05-25: ca. 13 GB
|
|---|
| 115 | 'TestResultFiles', # 2016-05-25: ca. 87 MB
|
|---|
| 116 | 'TestResultMsgs', # 2016-05-25: ca. 29 MB
|
|---|
| 117 | 'TestResultValues', # 2016-05-25: ca. 3728 MB
|
|---|
| 118 | 'TestResultFailures',
|
|---|
| 119 | 'SystemLog',
|
|---|
| 120 | ];
|
|---|
| 121 |
|
|---|
| 122 | def _doCopyTo(self, sTable, oZipFile, oDb, sSql, aoArgs = None):
|
|---|
| 123 | """ Does one COPY TO job. """
|
|---|
| 124 | print 'Dumping %s...' % (sTable,);
|
|---|
| 125 |
|
|---|
| 126 | if aoArgs is not None:
|
|---|
| 127 | sSql = oDb.formatBindArgs(sSql, aoArgs);
|
|---|
| 128 |
|
|---|
| 129 | oFile = open(self.oConfig.sTempFile, 'w');
|
|---|
| 130 | oDb.copyExpert(sSql, oFile);
|
|---|
| 131 | cRows = oDb.getRowCount();
|
|---|
| 132 | oFile.close();
|
|---|
| 133 | print '... %s rows.' % (cRows,);
|
|---|
| 134 |
|
|---|
| 135 | oZipFile.write(self.oConfig.sTempFile, sTable);
|
|---|
| 136 | return True;
|
|---|
| 137 |
|
|---|
| 138 | def _doDump(self, oDb):
|
|---|
| 139 | """ Does the dumping of the database. """
|
|---|
| 140 |
|
|---|
| 141 | oZipFile = zipfile.ZipFile(self.oConfig.sFilename, 'w', zipfile.ZIP_DEFLATED);
|
|---|
| 142 |
|
|---|
| 143 | oDb.begin();
|
|---|
| 144 |
|
|---|
| 145 | # Dumping full tables is simple.
|
|---|
| 146 | for sTable in self.kasTablesToDumpInFull:
|
|---|
| 147 | self._doCopyTo(sTable, oZipFile, oDb, 'COPY ' + sTable + ' TO STDOUT WITH (FORMAT TEXT)');
|
|---|
| 148 |
|
|---|
| 149 | # Figure out how far back we need to go.
|
|---|
| 150 | oDb.execute('SELECT CURRENT_TIMESTAMP - INTERVAL \'%s days\'' % (self.oConfig.cDays,));
|
|---|
| 151 | tsEffective = oDb.fetchOne()[0];
|
|---|
| 152 | oDb.execute('SELECT CURRENT_TIMESTAMP - INTERVAL \'%s days\'' % (self.oConfig.cDays + 2,));
|
|---|
| 153 | tsEffectiveSafe = oDb.fetchOne()[0];
|
|---|
| 154 | print 'Going back to: %s (safe: %s)' % (tsEffective, tsEffectiveSafe);
|
|---|
| 155 |
|
|---|
| 156 | # We dump test boxes back to the safe timestamp because the test sets may
|
|---|
| 157 | # use slightly dated test box references and we don't wish to have dangling
|
|---|
| 158 | # references when loading.
|
|---|
| 159 | for sTable in [ 'TestBoxes', ]:
|
|---|
| 160 | self._doCopyTo(sTable, oZipFile, oDb,
|
|---|
| 161 | 'COPY (SELECT * FROM ' + sTable + ' WHERE tsExpire >= %s) TO STDOUT WITH (FORMAT TEXT)',
|
|---|
| 162 | (tsEffectiveSafe,));
|
|---|
| 163 |
|
|---|
| 164 | # The test results needs to start with test sets and then dump everything
|
|---|
| 165 | # releated to them. So, figure the lowest (oldest) test set ID we'll be
|
|---|
| 166 | # dumping first.
|
|---|
| 167 | oDb.execute('SELECT idTestSet FROM TestSets WHERE tsCreated >= %s', (tsEffective, ));
|
|---|
| 168 | idFirstTestSet = 0;
|
|---|
| 169 | if oDb.getRowCount() > 0:
|
|---|
| 170 | idFirstTestSet = oDb.fetchOne()[0];
|
|---|
| 171 | print 'First test set ID: %s' % (idFirstTestSet,);
|
|---|
| 172 |
|
|---|
| 173 | oDb.execute('SELECT MAX(idTestSet) FROM TestSets WHERE tsCreated >= %s', (tsEffective, ));
|
|---|
| 174 | idLastTestSet = 0;
|
|---|
| 175 | if oDb.getRowCount() > 0:
|
|---|
| 176 | idLastTestSet = oDb.fetchOne()[0];
|
|---|
| 177 | print 'Last test set ID: %s' % (idLastTestSet,);
|
|---|
| 178 |
|
|---|
| 179 | oDb.execute('SELECT MAX(idTestResult) FROM TestResults WHERE tsCreated >= %s', (tsEffective, ));
|
|---|
| 180 | idLastTestResult = 0;
|
|---|
| 181 | if oDb.getRowCount() > 0:
|
|---|
| 182 | idLastTestResult = oDb.fetchOne()[0];
|
|---|
| 183 | print 'Last test result ID: %s' % (idLastTestResult,);
|
|---|
| 184 |
|
|---|
| 185 |
|
|---|
| 186 | # Tables with idTestSet member.
|
|---|
| 187 | for sTable in [ 'TestSets', 'TestResults', 'TestResultValues' ]:
|
|---|
| 188 | self._doCopyTo(sTable, oZipFile, oDb,
|
|---|
| 189 | 'COPY (SELECT *\n'
|
|---|
| 190 | ' FROM ' + sTable + '\n'
|
|---|
| 191 | ' WHERE idTestSet >= %s\n'
|
|---|
| 192 | ' AND idTestSet <= %s\n'
|
|---|
| 193 | ' AND idTestResult <= %s\n'
|
|---|
| 194 | ') TO STDOUT WITH (FORMAT TEXT)'
|
|---|
| 195 | , ( idFirstTestSet, idLastTestSet, idLastTestResult,));
|
|---|
| 196 |
|
|---|
| 197 | # Tables where we have to go via TestResult.
|
|---|
| 198 | for sTable in [ 'TestResultFiles', 'TestResultMsgs', 'TestResultFailures' ]:
|
|---|
| 199 | self._doCopyTo(sTable, oZipFile, oDb,
|
|---|
| 200 | 'COPY (SELECT it.*\n'
|
|---|
| 201 | ' FROM ' + sTable + ' it, TestResults tr\n'
|
|---|
| 202 | ' WHERE tr.idTestSet >= %s\n'
|
|---|
| 203 | ' AND tr.idTestSet <= %s\n'
|
|---|
| 204 | ' AND tr.idTestResult <= %s\n'
|
|---|
| 205 | ' AND tr.tsCreated >= %s\n' # performance hack.
|
|---|
| 206 | ' AND it.idTestResult = tr.idTestResult\n'
|
|---|
| 207 | ') TO STDOUT WITH (FORMAT TEXT)'
|
|---|
| 208 | , ( idFirstTestSet, idLastTestSet, idLastTestResult, tsEffective,));
|
|---|
| 209 |
|
|---|
| 210 | # Tables which goes exclusively by tsCreated.
|
|---|
| 211 | for sTable in [ 'SystemLog', ]:
|
|---|
| 212 | self._doCopyTo(sTable, oZipFile, oDb,
|
|---|
| 213 | 'COPY (SELECT * FROM ' + sTable + ' WHERE tsCreated >= %s) TO STDOUT WITH (FORMAT TEXT)',
|
|---|
| 214 | (tsEffective,));
|
|---|
| 215 |
|
|---|
| 216 | oZipFile.close();
|
|---|
| 217 | print "Done!";
|
|---|
| 218 | return 0;
|
|---|
| 219 |
|
|---|
| 220 | def _doLoad(self, oDb):
|
|---|
| 221 | """ Does the loading of the dumped data into the database. """
|
|---|
| 222 |
|
|---|
| 223 | oZipFile = zipfile.ZipFile(self.oConfig.sFilename, 'r');
|
|---|
| 224 |
|
|---|
| 225 | asTablesInLoadOrder = [
|
|---|
| 226 | 'Users',
|
|---|
| 227 | 'BuildBlacklist',
|
|---|
| 228 | 'BuildCategories',
|
|---|
| 229 | 'BuildSources',
|
|---|
| 230 | 'FailureCategories',
|
|---|
| 231 | 'FailureReasons',
|
|---|
| 232 | 'GlobalResources',
|
|---|
| 233 | 'Testcases',
|
|---|
| 234 | 'TestcaseArgs',
|
|---|
| 235 | 'TestcaseDeps',
|
|---|
| 236 | 'TestcaseGlobalRsrcDeps',
|
|---|
| 237 | 'TestGroups',
|
|---|
| 238 | 'TestGroupMembers',
|
|---|
| 239 | 'SchedGroups',
|
|---|
| 240 | 'TestBoxStrTab',
|
|---|
| 241 | 'TestBoxes',
|
|---|
| 242 | 'SchedGroupMembers',
|
|---|
| 243 | 'SchedQueues',
|
|---|
| 244 | 'Builds',
|
|---|
| 245 | 'SystemLog',
|
|---|
| 246 | 'VcsRevisions',
|
|---|
| 247 | 'TestResultStrTab',
|
|---|
| 248 | 'TestSets',
|
|---|
| 249 | 'TestResults',
|
|---|
| 250 | 'TestResultFiles',
|
|---|
| 251 | 'TestResultMsgs',
|
|---|
| 252 | 'TestResultValues',
|
|---|
| 253 | 'TestResultFailures',
|
|---|
| 254 | ];
|
|---|
| 255 | assert len(asTablesInLoadOrder) == len(self.kasTablesToDumpInFull) + len(self.kasTablesToPartiallyDump);
|
|---|
| 256 |
|
|---|
| 257 | oDb.begin();
|
|---|
| 258 | oDb.execute('SET CONSTRAINTS ALL DEFERRED;');
|
|---|
| 259 |
|
|---|
| 260 | print 'Checking if the database looks empty...\n'
|
|---|
| 261 | for sTable in asTablesInLoadOrder + [ 'TestBoxStatuses', 'GlobalResourceStatuses' ]:
|
|---|
| 262 | oDb.execute('SELECT COUNT(*) FROM ' + sTable);
|
|---|
| 263 | cRows = oDb.fetchOne()[0];
|
|---|
| 264 | cMaxRows = 0;
|
|---|
| 265 | if sTable in [ 'SchedGroups', 'TestBoxStrTab', 'TestResultStrTab', 'Users' ]: cMaxRows = 1;
|
|---|
| 266 | if cRows > cMaxRows:
|
|---|
| 267 | print 'error: Table %s has %u rows which is more than %u - refusing to delete and load.' \
|
|---|
| 268 | % (sTable, cRows, cMaxRows,);
|
|---|
| 269 | print 'info: Please drop and recreate the database before loading!'
|
|---|
| 270 | return 1;
|
|---|
| 271 |
|
|---|
| 272 | print 'Dropping default table content...\n'
|
|---|
| 273 | for sTable in [ 'SchedGroups', 'TestBoxStrTab', 'TestResultStrTab', 'Users']:
|
|---|
| 274 | oDb.execute('DELETE FROM ' + sTable);
|
|---|
| 275 |
|
|---|
| 276 | oDb.execute('ALTER TABLE TestSets DROP CONSTRAINT IF EXISTS TestSets_idTestResult_fkey');
|
|---|
| 277 |
|
|---|
| 278 | for sTable in asTablesInLoadOrder:
|
|---|
| 279 | print 'Loading %s...' % (sTable,);
|
|---|
| 280 | oFile = oZipFile.open(sTable);
|
|---|
| 281 | oDb.copyExpert('COPY ' + sTable + ' FROM STDIN WITH (FORMAT TEXT)', oFile);
|
|---|
| 282 | cRows = oDb.getRowCount();
|
|---|
| 283 | print '... %s rows.' % (cRows,);
|
|---|
| 284 |
|
|---|
| 285 | oDb.execute('ALTER TABLE TestSets ADD FOREIGN KEY (idTestResult) REFERENCES TestResults(idTestResult)');
|
|---|
| 286 | oDb.commit();
|
|---|
| 287 |
|
|---|
| 288 | # Correct sequences.
|
|---|
| 289 | atSequences = [
|
|---|
| 290 | ( 'UserIdSeq', 'Users', 'uid' ),
|
|---|
| 291 | ( 'GlobalResourceIdSeq', 'GlobalResources', 'idGlobalRsrc' ),
|
|---|
| 292 | ( 'BuildSourceIdSeq', 'BuildSources', 'idBuildSrc' ),
|
|---|
| 293 | ( 'TestCaseIdSeq', 'TestCases', 'idTestCase' ),
|
|---|
| 294 | ( 'TestCaseGenIdSeq', 'TestCases', 'idGenTestCase' ),
|
|---|
| 295 | ( 'TestCaseArgsIdSeq', 'TestCaseArgs', 'idTestCaseArgs' ),
|
|---|
| 296 | ( 'TestCaseArgsGenIdSeq', 'TestCaseArgs', 'idGenTestCaseArgs' ),
|
|---|
| 297 | ( 'TestGroupIdSeq', 'TestGroups', 'idTestGroup' ),
|
|---|
| 298 | ( 'SchedGroupIdSeq', 'SchedGroups', 'idSchedGroup' ),
|
|---|
| 299 | ( 'TestBoxStrTabIdSeq', 'TestBoxStrTab', 'idStr' ),
|
|---|
| 300 | ( 'TestBoxIdSeq', 'TestBoxes', 'idTestBox' ),
|
|---|
| 301 | ( 'TestBoxGenIdSeq', 'TestBoxes', 'idGenTestBox' ),
|
|---|
| 302 | ( 'FailureCategoryIdSeq', 'FailureCategories', 'idFailureCategory' ),
|
|---|
| 303 | ( 'FailureReasonIdSeq', 'FailureReasons', 'idFailureReason' ),
|
|---|
| 304 | ( 'BuildBlacklistIdSeq', 'BuildBlacklist', 'idBlacklisting' ),
|
|---|
| 305 | ( 'BuildCategoryIdSeq', 'BuildCategories', 'idBuildCategory' ),
|
|---|
| 306 | ( 'BuildIdSeq', 'Builds', 'idBuild' ),
|
|---|
| 307 | ( 'TestResultStrTabIdSeq', 'TestResultStrTab', 'idStr' ),
|
|---|
| 308 | ( 'TestResultIdSeq', 'TestResults', 'idTestResult' ),
|
|---|
| 309 | ( 'TestResultValueIdSeq', 'TestResultValues', 'idTestResultValue' ),
|
|---|
| 310 | ( 'TestResultFileId', 'TestResultFiles', 'idTestResultFile' ),
|
|---|
| 311 | ( 'TestResultMsgIdSeq', 'TestResultMsgs', 'idTestResultMsg' ),
|
|---|
| 312 | ( 'TestSetIdSeq', 'TestSets', 'idTestSet' ),
|
|---|
| 313 | ( 'SchedQueueItemIdSeq', 'SchedQueues', 'idItem' ),
|
|---|
| 314 | ];
|
|---|
| 315 | for (sSeq, sTab, sCol) in atSequences:
|
|---|
| 316 | oDb.execute('SELECT MAX(%s) FROM %s' % (sCol, sTab,));
|
|---|
| 317 | idMax = oDb.fetchOne()[0];
|
|---|
| 318 | print '%s: idMax=%s' % (sSeq, idMax);
|
|---|
| 319 | if idMax is not None:
|
|---|
| 320 | oDb.execute('SELECT setval(\'%s\', %s)' % (sSeq, idMax));
|
|---|
| 321 |
|
|---|
| 322 | # Last step.
|
|---|
| 323 | print 'Analyzing...'
|
|---|
| 324 | oDb.execute('ANALYZE');
|
|---|
| 325 | oDb.commit();
|
|---|
| 326 |
|
|---|
| 327 | print 'Done!'
|
|---|
| 328 | return 0;
|
|---|
| 329 |
|
|---|
| 330 | def main(self):
|
|---|
| 331 | """
|
|---|
| 332 | Main function.
|
|---|
| 333 | """
|
|---|
| 334 | oDb = TMDatabaseConnection();
|
|---|
| 335 |
|
|---|
| 336 | if self.oConfig.fLoadDumpIntoDatabase is not True:
|
|---|
| 337 | rc = self._doDump(oDb);
|
|---|
| 338 | else:
|
|---|
| 339 | rc = self._doLoad(oDb);
|
|---|
| 340 |
|
|---|
| 341 | oDb.close();
|
|---|
| 342 | return 0;
|
|---|
| 343 |
|
|---|
| 344 | if __name__ == '__main__':
|
|---|
| 345 | sys.exit(PartialDbDump().main());
|
|---|
| 346 |
|
|---|
