partial-db-dump.py@ 86989

Last change on this file since 86989 was 86989, checked in by vboxsync, 4 years ago
testmanager/partial-db-dump.py: Only dump what we need from TestBoxStrTab. [fix] 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: 15.8 KB

Line
1	#!/usr/bin/env python
2	# -- coding: utf-8 --
3	# $Id: partial-db-dump.py 86989 2020-11-26 14:27:25Z 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: 86989 $"
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	'Testcases',
94	'TestcaseArgs',
95	'TestcaseDeps',
96	'TestcaseGlobalRsrcDeps',
97	'TestGroups',
98	'TestGroupMembers',
99	'SchedGroups',
100	'SchedGroupMembers', # ?
101	'TestBoxesInSchedGroups', # ?
102	'SchedQueues',
103	'VcsRevisions', # ?
104	'TestResultStrTab', # 36K rows, never mind complicated then.
105	];
106
107	##
108	# Tables where we only dump partial info (the TestResult* tables are rather
109	# gigantic).
110	kasTablesToPartiallyDump = [
111	'TestBoxes', # 2016-05-25: ca. 641 MB
112	'TestSets', # 2016-05-25: ca. 525 MB
113	'TestResults', # 2016-05-25: ca. 13 GB
114	'TestResultFiles', # 2016-05-25: ca. 87 MB
115	'TestResultMsgs', # 2016-05-25: ca. 29 MB
116	'TestResultValues', # 2016-05-25: ca. 3728 MB
117	'TestResultFailures',
118	'Builds',
119	'TestBoxStrTab',
120	'SystemLog',
121	];
122
123	def _doCopyTo(self, sTable, oZipFile, oDb, sSql, aoArgs = None):
124	""" Does one COPY TO job. """
125	print('Dumping %s...' % (sTable,));
126
127	if aoArgs is not None:
128	sSql = oDb.formatBindArgs(sSql, aoArgs);
129
130	oFile = open(self.oConfig.sTempFile, 'w');
131	oDb.copyExpert(sSql, oFile);
132	cRows = oDb.getRowCount();
133	oFile.close();
134	print('... %s rows.' % (cRows,));
135
136	oZipFile.write(self.oConfig.sTempFile, sTable);
137	return True;
138
139	def _doDump(self, oDb):
140	""" Does the dumping of the database. """
141
142	oZipFile = zipfile.ZipFile(self.oConfig.sFilename, 'w', zipfile.ZIP_DEFLATED);
143
144	oDb.begin();
145
146	# Dumping full tables is simple.
147	for sTable in self.kasTablesToDumpInFull:
148	self._doCopyTo(sTable, oZipFile, oDb, 'COPY ' + sTable + ' TO STDOUT WITH (FORMAT TEXT)');
149
150	# Figure out how far back we need to go.
151	oDb.execute('SELECT CURRENT_TIMESTAMP - INTERVAL \'%s days\'' % (self.oConfig.cDays,));
152	tsEffective = oDb.fetchOne()[0];
153	oDb.execute('SELECT CURRENT_TIMESTAMP - INTERVAL \'%s days\'' % (self.oConfig.cDays + 2,));
154	tsEffectiveSafe = oDb.fetchOne()[0];
155	print('Going back to: %s (safe: %s)' % (tsEffective, tsEffectiveSafe));
156
157	# We dump test boxes back to the safe timestamp because the test sets may
158	# use slightly dated test box references and we don't wish to have dangling
159	# references when loading.
160	for sTable in [ 'TestBoxes', ]:
161	self._doCopyTo(sTable, oZipFile, oDb,
162	'COPY (SELECT * FROM ' + sTable + ' WHERE tsExpire >= %s) TO STDOUT WITH (FORMAT TEXT)',
163	(tsEffectiveSafe,));
164
165	# The test results needs to start with test sets and then dump everything
166	# releated to them. So, figure the lowest (oldest) test set ID we'll be
167	# dumping first.
168	oDb.execute('SELECT idTestSet FROM TestSets WHERE tsCreated >= %s', (tsEffective, ));
169	idFirstTestSet = 0;
170	if oDb.getRowCount() > 0:
171	idFirstTestSet = oDb.fetchOne()[0];
172	print('First test set ID: %s' % (idFirstTestSet,));
173
174	oDb.execute('SELECT MAX(idTestSet) FROM TestSets WHERE tsCreated >= %s', (tsEffective, ));
175	idLastTestSet = 0;
176	if oDb.getRowCount() > 0:
177	idLastTestSet = oDb.fetchOne()[0];
178	print('Last test set ID: %s' % (idLastTestSet,));
179
180	oDb.execute('SELECT MAX(idTestResult) FROM TestResults WHERE tsCreated >= %s', (tsEffective, ));
181	idLastTestResult = 0;
182	if oDb.getRowCount() > 0:
183	idLastTestResult = oDb.fetchOne()[0];
184	print('Last test result ID: %s' % (idLastTestResult,));
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	# The builds table.
217	oDb.execute('SELECT MIN(idBuild), MIN(idBuildTestSuite) FROM TestSets WHERE idTestSet >= %s', (idFirstTestSet,));
218	idFirstBuild = 0;
219	if oDb.getRowCount() > 0:
220	idFirstBuild = min(oDb.fetchOne());
221	print('First build ID: %s' % (idFirstBuild,));
222	for sTable in [ 'Builds', ]:
223	self._doCopyTo(sTable, oZipFile, oDb,
224	'COPY (SELECT * FROM ' + sTable + ' WHERE idBuild >= %s) TO STDOUT WITH (FORMAT TEXT)',
225	(idFirstBuild,));
226
227	# The test box string table.
228	self._doCopyTo('TestBoxStrTab', oZipFile, oDb, '''
229	COPY (SELECT * FROM TestBoxStrTab WHERE idStr IN (
230	( SELECT 0
231	) UNION ( SELECT idStrComment FROM TestBoxes WHERE tsExpire >= %s
232	) UNION ( SELECT idStrCpuArch FROM TestBoxes WHERE tsExpire >= %s
233	) UNION ( SELECT idStrCpuName FROM TestBoxes WHERE tsExpire >= %s
234	) UNION ( SELECT idStrCpuVendor FROM TestBoxes WHERE tsExpire >= %s
235	) UNION ( SELECT idStrDescription FROM TestBoxes WHERE tsExpire >= %s
236	) UNION ( SELECT idStrOS FROM TestBoxes WHERE tsExpire >= %s
237	) UNION ( SELECT idStrOsVersion FROM TestBoxes WHERE tsExpire >= %s
238	) UNION ( SELECT idStrReport FROM TestBoxes WHERE tsExpire >= %s
239	) ) ) TO STDOUT WITH (FORMAT TEXT)
240	''', (tsEffectiveSafe, tsEffectiveSafe, tsEffectiveSafe, tsEffectiveSafe,
241	tsEffectiveSafe, tsEffectiveSafe, tsEffectiveSafe, tsEffectiveSafe,));
242
243	oZipFile.close();
244	print('Done!');
245	return 0;
246
247	def _doLoad(self, oDb):
248	""" Does the loading of the dumped data into the database. """
249
250	oZipFile = zipfile.ZipFile(self.oConfig.sFilename, 'r');
251
252	asTablesInLoadOrder = [
253	'Users',
254	'BuildBlacklist',
255	'BuildCategories',
256	'BuildSources',
257	'FailureCategories',
258	'FailureReasons',
259	'GlobalResources',
260	'Testcases',
261	'TestcaseArgs',
262	'TestcaseDeps',
263	'TestcaseGlobalRsrcDeps',
264	'TestGroups',
265	'TestGroupMembers',
266	'SchedGroups',
267	'TestBoxes',
268	'SchedGroupMembers',
269	'TestBoxesInSchedGroups',
270	'SchedQueues',
271	'Builds',
272	'SystemLog',
273	'VcsRevisions',
274	'TestResultStrTab',
275	'TestSets',
276	'TestResults',
277	'TestResultFiles',
278	'TestResultMsgs',
279	'TestResultValues',
280	'TestResultFailures',
281	];
282	assert len(asTablesInLoadOrder) == len(self.kasTablesToDumpInFull) + len(self.kasTablesToPartiallyDump);
283
284	oDb.begin();
285	oDb.execute('SET CONSTRAINTS ALL DEFERRED;');
286
287	print('Checking if the database looks empty...\n');
288	for sTable in asTablesInLoadOrder + [ 'TestBoxStatuses', 'GlobalResourceStatuses' ]:
289	oDb.execute('SELECT COUNT(*) FROM ' + sTable);
290	cRows = oDb.fetchOne()[0];
291	cMaxRows = 0;
292	if sTable in [ 'SchedGroups', 'TestBoxStrTab', 'TestResultStrTab', 'Users' ]: cMaxRows = 1;
293	if cRows > cMaxRows:
294	print('error: Table %s has %u rows which is more than %u - refusing to delete and load.'
295	% (sTable, cRows, cMaxRows,));
296	print('info: Please drop and recreate the database before loading!')
297	return 1;
298
299	print('Dropping default table content...\n');
300	for sTable in [ 'SchedGroups', 'TestBoxStrTab', 'TestResultStrTab', 'Users']:
301	oDb.execute('DELETE FROM ' + sTable);
302
303	oDb.execute('ALTER TABLE TestSets DROP CONSTRAINT IF EXISTS TestSets_idTestResult_fkey');
304
305	for sTable in asTablesInLoadOrder:
306	print('Loading %s...' % (sTable,));
307	oFile = oZipFile.open(sTable);
308	oDb.copyExpert('COPY ' + sTable + ' FROM STDIN WITH (FORMAT TEXT)', oFile);
309	cRows = oDb.getRowCount();
310	print('... %s rows.' % (cRows,));
311
312	oDb.execute('ALTER TABLE TestSets ADD FOREIGN KEY (idTestResult) REFERENCES TestResults(idTestResult)');
313	oDb.commit();
314
315	# Correct sequences.
316	atSequences = [
317	( 'UserIdSeq', 'Users', 'uid' ),
318	( 'GlobalResourceIdSeq', 'GlobalResources', 'idGlobalRsrc' ),
319	( 'BuildSourceIdSeq', 'BuildSources', 'idBuildSrc' ),
320	( 'TestCaseIdSeq', 'TestCases', 'idTestCase' ),
321	( 'TestCaseGenIdSeq', 'TestCases', 'idGenTestCase' ),
322	( 'TestCaseArgsIdSeq', 'TestCaseArgs', 'idTestCaseArgs' ),
323	( 'TestCaseArgsGenIdSeq', 'TestCaseArgs', 'idGenTestCaseArgs' ),
324	( 'TestGroupIdSeq', 'TestGroups', 'idTestGroup' ),
325	( 'SchedGroupIdSeq', 'SchedGroups', 'idSchedGroup' ),
326	( 'TestBoxStrTabIdSeq', 'TestBoxStrTab', 'idStr' ),
327	( 'TestBoxIdSeq', 'TestBoxes', 'idTestBox' ),
328	( 'TestBoxGenIdSeq', 'TestBoxes', 'idGenTestBox' ),
329	( 'FailureCategoryIdSeq', 'FailureCategories', 'idFailureCategory' ),
330	( 'FailureReasonIdSeq', 'FailureReasons', 'idFailureReason' ),
331	( 'BuildBlacklistIdSeq', 'BuildBlacklist', 'idBlacklisting' ),
332	( 'BuildCategoryIdSeq', 'BuildCategories', 'idBuildCategory' ),
333	( 'BuildIdSeq', 'Builds', 'idBuild' ),
334	( 'TestResultStrTabIdSeq', 'TestResultStrTab', 'idStr' ),
335	( 'TestResultIdSeq', 'TestResults', 'idTestResult' ),
336	( 'TestResultValueIdSeq', 'TestResultValues', 'idTestResultValue' ),
337	( 'TestResultFileId', 'TestResultFiles', 'idTestResultFile' ),
338	( 'TestResultMsgIdSeq', 'TestResultMsgs', 'idTestResultMsg' ),
339	( 'TestSetIdSeq', 'TestSets', 'idTestSet' ),
340	( 'SchedQueueItemIdSeq', 'SchedQueues', 'idItem' ),
341	];
342	for (sSeq, sTab, sCol) in atSequences:
343	oDb.execute('SELECT MAX(%s) FROM %s' % (sCol, sTab,));
344	idMax = oDb.fetchOne()[0];
345	print('%s: idMax=%s' % (sSeq, idMax));
346	if idMax is not None:
347	oDb.execute('SELECT setval(\'%s\', %s)' % (sSeq, idMax));
348
349	# Last step.
350	print('Analyzing...');
351	oDb.execute('ANALYZE');
352	oDb.commit();
353
354	print('Done!');
355	return 0;
356
357	def main(self):
358	"""
359	Main function.
360	"""
361	oDb = TMDatabaseConnection();
362
363	if self.oConfig.fLoadDumpIntoDatabase is not True:
364	rc = self._doDump(oDb);
365	else:
366	rc = self._doLoad(oDb);
367
368	oDb.close();
369	return 0;
370
371	if __name__ == '__main__':
372	sys.exit(PartialDbDump().main());
373

Note: See TracBrowser for help on using the repository browser.

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

Download in other formats: