1 | #!/usr/bin/env python
|
---|
2 | # -*- coding: utf-8 -*-
|
---|
3 | # $Id: partial-db-dump.py 86992 2020-11-26 14:58:19Z 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-2020 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: 86992 $"
|
---|
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 | oParser.add_option('--lmza', dest = 'fLmza', action = 'store_true',
|
---|
79 | default = False, help = 'Use LMZA instead of Deflate compression.');
|
---|
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.fLmza:
|
---|
146 | enmCompression = getattr(zipfile. 'ZIP_LZMA', zipfile.ZIP_DEFLATED);
|
---|
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, '''
|
---|
234 | COPY (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 |
|
---|
376 | if __name__ == '__main__':
|
---|
377 | sys.exit(PartialDbDump().main());
|
---|
378 |
|
---|