VirtualBox

source: vbox/trunk/src/VBox/ValidationKit/testmanager/db/partial-db-dump.py@ 92892

Last change on this file since 92892 was 86994, checked in by vboxsync, 4 years ago

testmanager/partial-db-dump.py: LZMA is too slow, replaced the option with a --store so the backup program can use zx to compress it more efficiently. bugref:9788
  • Property svn:eol-style set to LF
  • Property svn:executable set to *
  • Property svn:keywords set to Author Date Id Revision
File size: 16.1 KB
Line 
1#!/usr/bin/env python
2# -*- coding: utf-8 -*-
3# $Id: partial-db-dump.py 86994 2020-11-26 15:12:03Z vboxsync $
4# pylint: disable=line-too-long
5
6"""
7Utility for dumping the last X days of data.
8"""
9
10__copyright__ = \
11"""
12Copyright (C) 2012-2020 Oracle Corporation
13
14This file is part of VirtualBox Open Source Edition (OSE), as
15available from http://www.virtualbox.org. This file is free software;
16you can redistribute it and/or modify it under the terms of the GNU
17General Public License (GPL) as published by the Free Software
18Foundation, in version 2 as it comes in the "COPYING" file of the
19VirtualBox OSE distribution. VirtualBox OSE is distributed in the
20hope that it will be useful, but WITHOUT ANY WARRANTY of any kind.
21
22The contents of this file may alternatively be used under the terms
23of the Common Development and Distribution License Version 1.0
24(CDDL) only, as it comes in the "COPYING.CDDL" file of the
25VirtualBox OSE distribution, in which case the provisions of the
26CDDL are applicable instead of those of the GPL.
27
28You may elect to license modified versions of this file under the
29terms and conditions of either the GPL or the CDDL or both.
30"""
31__version__ = "$Revision: 86994 $"
32
33# Standard python imports
34import sys;
35import os;
36import zipfile;
37from optparse import OptionParser;
38import xml.etree.ElementTree as ET;
39
40# Add Test Manager's modules path
41g_ksTestManagerDir = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))));
42sys.path.append(g_ksTestManagerDir);
43
44# Test Manager imports
45from testmanager.core.db import TMDatabaseConnection;
46from common import utils;
47
48
49class 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 oParser.add_option('--store', dest = 'fStore', action = 'store_true',
79 default = False, help = 'Do not compress the zip file.');
80
81 (self.oConfig, _) = oParser.parse_args();
82
83
84 ##
85 # Tables dumped in full because they're either needed in full or they normally
86 # aren't large enough to bother reducing.
87 kasTablesToDumpInFull = [
88 'Users',
89 'BuildBlacklist',
90 'BuildCategories',
91 'BuildSources',
92 'FailureCategories',
93 'FailureReasons',
94 'GlobalResources',
95 'Testcases',
96 'TestcaseArgs',
97 'TestcaseDeps',
98 'TestcaseGlobalRsrcDeps',
99 'TestGroups',
100 'TestGroupMembers',
101 'SchedGroups',
102 'SchedGroupMembers', # ?
103 'TestBoxesInSchedGroups', # ?
104 'SchedQueues',
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 'Builds',
120 'TestBoxStrTab',
121 'SystemLog',
122 'VcsRevisions',
123 ];
124
125 def _doCopyTo(self, sTable, oZipFile, oDb, sSql, aoArgs = None):
126 """ Does one COPY TO job. """
127 print('Dumping %s...' % (sTable,));
128
129 if aoArgs is not None:
130 sSql = oDb.formatBindArgs(sSql, aoArgs);
131
132 oFile = open(self.oConfig.sTempFile, 'w');
133 oDb.copyExpert(sSql, oFile);
134 cRows = oDb.getRowCount();
135 oFile.close();
136 print('... %s rows.' % (cRows,));
137
138 oZipFile.write(self.oConfig.sTempFile, sTable);
139 return True;
140
141 def _doDump(self, oDb):
142 """ Does the dumping of the database. """
143
144 enmCompression = zipfile.ZIP_DEFLATED;
145 if self.oConfig.fStore:
146 enmCompression = zipfile.ZIP_STORED;
147 oZipFile = zipfile.ZipFile(self.oConfig.sFilename, 'w', enmCompression);
148
149 oDb.begin();
150
151 # Dumping full tables is simple.
152 for sTable in self.kasTablesToDumpInFull:
153 self._doCopyTo(sTable, oZipFile, oDb, 'COPY ' + sTable + ' TO STDOUT WITH (FORMAT TEXT)');
154
155 # Figure out how far back we need to go.
156 oDb.execute('SELECT CURRENT_TIMESTAMP - INTERVAL \'%s days\'' % (self.oConfig.cDays,));
157 tsEffective = oDb.fetchOne()[0];
158 oDb.execute('SELECT CURRENT_TIMESTAMP - INTERVAL \'%s days\'' % (self.oConfig.cDays + 2,));
159 tsEffectiveSafe = oDb.fetchOne()[0];
160 print('Going back to: %s (safe: %s)' % (tsEffective, tsEffectiveSafe));
161
162 # We dump test boxes back to the safe timestamp because the test sets may
163 # use slightly dated test box references and we don't wish to have dangling
164 # references when loading.
165 for sTable in [ 'TestBoxes', ]:
166 self._doCopyTo(sTable, oZipFile, oDb,
167 'COPY (SELECT * FROM ' + sTable + ' WHERE tsExpire >= %s) TO STDOUT WITH (FORMAT TEXT)',
168 (tsEffectiveSafe,));
169
170 # The test results needs to start with test sets and then dump everything
171 # releated to them. So, figure the lowest (oldest) test set ID we'll be
172 # dumping first.
173 oDb.execute('SELECT idTestSet FROM TestSets WHERE tsCreated >= %s', (tsEffective, ));
174 idFirstTestSet = 0;
175 if oDb.getRowCount() > 0:
176 idFirstTestSet = oDb.fetchOne()[0];
177 print('First test set ID: %s' % (idFirstTestSet,));
178
179 oDb.execute('SELECT MAX(idTestSet) FROM TestSets WHERE tsCreated >= %s', (tsEffective, ));
180 idLastTestSet = 0;
181 if oDb.getRowCount() > 0:
182 idLastTestSet = oDb.fetchOne()[0];
183 print('Last test set ID: %s' % (idLastTestSet,));
184
185 oDb.execute('SELECT MAX(idTestResult) FROM TestResults WHERE tsCreated >= %s', (tsEffective, ));
186 idLastTestResult = 0;
187 if oDb.getRowCount() > 0:
188 idLastTestResult = oDb.fetchOne()[0];
189 print('Last test result ID: %s' % (idLastTestResult,));
190
191 # Tables with idTestSet member.
192 for sTable in [ 'TestSets', 'TestResults', 'TestResultValues' ]:
193 self._doCopyTo(sTable, oZipFile, oDb,
194 'COPY (SELECT *\n'
195 ' FROM ' + sTable + '\n'
196 ' WHERE idTestSet >= %s\n'
197 ' AND idTestSet <= %s\n'
198 ' AND idTestResult <= %s\n'
199 ') TO STDOUT WITH (FORMAT TEXT)'
200 , ( idFirstTestSet, idLastTestSet, idLastTestResult,));
201
202 # Tables where we have to go via TestResult.
203 for sTable in [ 'TestResultFiles', 'TestResultMsgs', 'TestResultFailures' ]:
204 self._doCopyTo(sTable, oZipFile, oDb,
205 'COPY (SELECT it.*\n'
206 ' FROM ' + sTable + ' it, TestResults tr\n'
207 ' WHERE tr.idTestSet >= %s\n'
208 ' AND tr.idTestSet <= %s\n'
209 ' AND tr.idTestResult <= %s\n'
210 ' AND tr.tsCreated >= %s\n' # performance hack.
211 ' AND it.idTestResult = tr.idTestResult\n'
212 ') TO STDOUT WITH (FORMAT TEXT)'
213 , ( idFirstTestSet, idLastTestSet, idLastTestResult, tsEffective,));
214
215 # Tables which goes exclusively by tsCreated using tsEffectiveSafe.
216 for sTable in [ 'SystemLog', 'VcsRevisions' ]:
217 self._doCopyTo(sTable, oZipFile, oDb,
218 'COPY (SELECT * FROM ' + sTable + ' WHERE tsCreated >= %s) TO STDOUT WITH (FORMAT TEXT)',
219 (tsEffectiveSafe,));
220
221 # The builds table.
222 oDb.execute('SELECT MIN(idBuild), MIN(idBuildTestSuite) FROM TestSets WHERE idTestSet >= %s', (idFirstTestSet,));
223 idFirstBuild = 0;
224 if oDb.getRowCount() > 0:
225 idFirstBuild = min(oDb.fetchOne());
226 print('First build ID: %s' % (idFirstBuild,));
227 for sTable in [ 'Builds', ]:
228 self._doCopyTo(sTable, oZipFile, oDb,
229 'COPY (SELECT * FROM ' + sTable + ' WHERE idBuild >= %s) TO STDOUT WITH (FORMAT TEXT)',
230 (idFirstBuild,));
231
232 # The test box string table.
233 self._doCopyTo('TestBoxStrTab', oZipFile, oDb, '''
234COPY (SELECT * FROM TestBoxStrTab WHERE idStr IN (
235 ( SELECT 0
236 ) UNION ( SELECT idStrComment FROM TestBoxes WHERE tsExpire >= %s
237 ) UNION ( SELECT idStrCpuArch FROM TestBoxes WHERE tsExpire >= %s
238 ) UNION ( SELECT idStrCpuName FROM TestBoxes WHERE tsExpire >= %s
239 ) UNION ( SELECT idStrCpuVendor FROM TestBoxes WHERE tsExpire >= %s
240 ) UNION ( SELECT idStrDescription FROM TestBoxes WHERE tsExpire >= %s
241 ) UNION ( SELECT idStrOS FROM TestBoxes WHERE tsExpire >= %s
242 ) UNION ( SELECT idStrOsVersion FROM TestBoxes WHERE tsExpire >= %s
243 ) UNION ( SELECT idStrReport FROM TestBoxes WHERE tsExpire >= %s
244 ) ) ) TO STDOUT WITH (FORMAT TEXT)
245''', (tsEffectiveSafe, tsEffectiveSafe, tsEffectiveSafe, tsEffectiveSafe,
246 tsEffectiveSafe, tsEffectiveSafe, tsEffectiveSafe, tsEffectiveSafe,));
247
248 oZipFile.close();
249 print('Done!');
250 return 0;
251
252 def _doLoad(self, oDb):
253 """ Does the loading of the dumped data into the database. """
254
255 oZipFile = zipfile.ZipFile(self.oConfig.sFilename, 'r');
256
257 asTablesInLoadOrder = [
258 'Users',
259 'BuildBlacklist',
260 'BuildCategories',
261 'BuildSources',
262 'FailureCategories',
263 'FailureReasons',
264 'GlobalResources',
265 'Testcases',
266 'TestcaseArgs',
267 'TestcaseDeps',
268 'TestcaseGlobalRsrcDeps',
269 'TestGroups',
270 'TestGroupMembers',
271 'SchedGroups',
272 'TestBoxes',
273 'SchedGroupMembers',
274 'TestBoxesInSchedGroups',
275 'SchedQueues',
276 'Builds',
277 'SystemLog',
278 'VcsRevisions',
279 'TestResultStrTab',
280 'TestSets',
281 'TestResults',
282 'TestResultFiles',
283 'TestResultMsgs',
284 'TestResultValues',
285 'TestResultFailures',
286 ];
287 assert len(asTablesInLoadOrder) == len(self.kasTablesToDumpInFull) + len(self.kasTablesToPartiallyDump);
288
289 oDb.begin();
290 oDb.execute('SET CONSTRAINTS ALL DEFERRED;');
291
292 print('Checking if the database looks empty...\n');
293 for sTable in asTablesInLoadOrder + [ 'TestBoxStatuses', 'GlobalResourceStatuses' ]:
294 oDb.execute('SELECT COUNT(*) FROM ' + sTable);
295 cRows = oDb.fetchOne()[0];
296 cMaxRows = 0;
297 if sTable in [ 'SchedGroups', 'TestBoxStrTab', 'TestResultStrTab', 'Users' ]: cMaxRows = 1;
298 if cRows > cMaxRows:
299 print('error: Table %s has %u rows which is more than %u - refusing to delete and load.'
300 % (sTable, cRows, cMaxRows,));
301 print('info: Please drop and recreate the database before loading!')
302 return 1;
303
304 print('Dropping default table content...\n');
305 for sTable in [ 'SchedGroups', 'TestBoxStrTab', 'TestResultStrTab', 'Users']:
306 oDb.execute('DELETE FROM ' + sTable);
307
308 oDb.execute('ALTER TABLE TestSets DROP CONSTRAINT IF EXISTS TestSets_idTestResult_fkey');
309
310 for sTable in asTablesInLoadOrder:
311 print('Loading %s...' % (sTable,));
312 oFile = oZipFile.open(sTable);
313 oDb.copyExpert('COPY ' + sTable + ' FROM STDIN WITH (FORMAT TEXT)', oFile);
314 cRows = oDb.getRowCount();
315 print('... %s rows.' % (cRows,));
316
317 oDb.execute('ALTER TABLE TestSets ADD FOREIGN KEY (idTestResult) REFERENCES TestResults(idTestResult)');
318 oDb.commit();
319
320 # Correct sequences.
321 atSequences = [
322 ( 'UserIdSeq', 'Users', 'uid' ),
323 ( 'GlobalResourceIdSeq', 'GlobalResources', 'idGlobalRsrc' ),
324 ( 'BuildSourceIdSeq', 'BuildSources', 'idBuildSrc' ),
325 ( 'TestCaseIdSeq', 'TestCases', 'idTestCase' ),
326 ( 'TestCaseGenIdSeq', 'TestCases', 'idGenTestCase' ),
327 ( 'TestCaseArgsIdSeq', 'TestCaseArgs', 'idTestCaseArgs' ),
328 ( 'TestCaseArgsGenIdSeq', 'TestCaseArgs', 'idGenTestCaseArgs' ),
329 ( 'TestGroupIdSeq', 'TestGroups', 'idTestGroup' ),
330 ( 'SchedGroupIdSeq', 'SchedGroups', 'idSchedGroup' ),
331 ( 'TestBoxStrTabIdSeq', 'TestBoxStrTab', 'idStr' ),
332 ( 'TestBoxIdSeq', 'TestBoxes', 'idTestBox' ),
333 ( 'TestBoxGenIdSeq', 'TestBoxes', 'idGenTestBox' ),
334 ( 'FailureCategoryIdSeq', 'FailureCategories', 'idFailureCategory' ),
335 ( 'FailureReasonIdSeq', 'FailureReasons', 'idFailureReason' ),
336 ( 'BuildBlacklistIdSeq', 'BuildBlacklist', 'idBlacklisting' ),
337 ( 'BuildCategoryIdSeq', 'BuildCategories', 'idBuildCategory' ),
338 ( 'BuildIdSeq', 'Builds', 'idBuild' ),
339 ( 'TestResultStrTabIdSeq', 'TestResultStrTab', 'idStr' ),
340 ( 'TestResultIdSeq', 'TestResults', 'idTestResult' ),
341 ( 'TestResultValueIdSeq', 'TestResultValues', 'idTestResultValue' ),
342 ( 'TestResultFileId', 'TestResultFiles', 'idTestResultFile' ),
343 ( 'TestResultMsgIdSeq', 'TestResultMsgs', 'idTestResultMsg' ),
344 ( 'TestSetIdSeq', 'TestSets', 'idTestSet' ),
345 ( 'SchedQueueItemIdSeq', 'SchedQueues', 'idItem' ),
346 ];
347 for (sSeq, sTab, sCol) in atSequences:
348 oDb.execute('SELECT MAX(%s) FROM %s' % (sCol, sTab,));
349 idMax = oDb.fetchOne()[0];
350 print('%s: idMax=%s' % (sSeq, idMax));
351 if idMax is not None:
352 oDb.execute('SELECT setval(\'%s\', %s)' % (sSeq, idMax));
353
354 # Last step.
355 print('Analyzing...');
356 oDb.execute('ANALYZE');
357 oDb.commit();
358
359 print('Done!');
360 return 0;
361
362 def main(self):
363 """
364 Main function.
365 """
366 oDb = TMDatabaseConnection();
367
368 if self.oConfig.fLoadDumpIntoDatabase is not True:
369 rc = self._doDump(oDb);
370 else:
371 rc = self._doLoad(oDb);
372
373 oDb.close();
374 return 0;
375
376if __name__ == '__main__':
377 sys.exit(PartialDbDump().main());
378
Note: See TracBrowser for help on using the repository browser.

© 2024 Oracle Support Privacy / Do Not Sell My Info Terms of Use Trademark Policy Automated Access Etiquette