1  # Copyright (c) 2017-2020 Krzysztof Kozlowski
 2  # Author: Krzysztof Kozlowski <k.kozlowski.k@gmail.com>
 3  #                             <krzk@kernel.org>
 4  #
 5  # SPDX-License-Identifier: GPL-2.0
 6  #
 7  
 8  # Dump entire buildbot DB:
 9  # mysqldump --add-drop-table --add-locks  --extended-insert --lock-tables -u buildbot -p -h localhost -P 3306 --protocol tcp buildbot > bck-db-buildbot-$(date +%Y-%m-%d).sql
10  
11  SELECT COUNT(*),project FROM buildbot.changes GROUP BY project;
12  
13  # next:
14  SELECT COUNT(*) FROM buildbot.changes
15  LEFT JOIN buildbot.sourcestamps ON buildbot.changes.sourcestampid = buildbot.sourcestamps.id
16  LEFT JOIN buildbot.change_files ON buildbot.changes.changeid = buildbot.change_files.changeid
17  WHERE
18    buildbot.changes.when_timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))
19    AND buildbot.changes.project = 'next';
20  
21  DELETE buildbot.changes, buildbot.sourcestamps, buildbot.change_files FROM buildbot.changes
22  LEFT JOIN buildbot.sourcestamps ON buildbot.changes.sourcestampid = buildbot.sourcestamps.id
23  LEFT JOIN buildbot.change_files ON buildbot.changes.changeid = buildbot.change_files.changeid
24  WHERE
25    buildbot.changes.when_timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))
26    AND buildbot.changes.project = 'next';
27  
28  # stable and stable-rc
29  SELECT COUNT(*) FROM buildbot.changes
30  LEFT JOIN buildbot.sourcestamps ON buildbot.changes.sourcestampid = buildbot.sourcestamps.id
31  LEFT JOIN buildbot.change_files ON buildbot.changes.changeid = buildbot.change_files.changeid
32  WHERE
33    buildbot.changes.when_timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 90 DAY))
34    AND (buildbot.changes.project = 'stable-rc' OR buildbot.changes.project = 'stable');
35  
36  DELETE buildbot.changes, buildbot.sourcestamps, buildbot.change_files FROM buildbot.changes
37  LEFT JOIN buildbot.sourcestamps ON buildbot.changes.sourcestampid = buildbot.sourcestamps.id
38  LEFT JOIN buildbot.change_files ON buildbot.changes.changeid = buildbot.change_files.changeid
39  WHERE
40    buildbot.changes.when_timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 90 DAY))
41    AND (buildbot.changes.project = 'stable-rc' OR buildbot.changes.project = 'stable');
42  
43  # krzk-github
44  SELECT COUNT(*) FROM buildbot.changes
45  LEFT JOIN buildbot.sourcestamps ON buildbot.changes.sourcestampid = buildbot.sourcestamps.id
46  LEFT JOIN buildbot.change_files ON buildbot.changes.changeid = buildbot.change_files.changeid
47  WHERE
48    buildbot.changes.when_timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY))
49    AND buildbot.changes.project = 'krzk-github';
50  
51  DELETE buildbot.changes, buildbot.sourcestamps, buildbot.change_files FROM buildbot.changes
52  LEFT JOIN buildbot.sourcestamps ON buildbot.changes.sourcestampid = buildbot.sourcestamps.id
53  LEFT JOIN buildbot.change_files ON buildbot.changes.changeid = buildbot.change_files.changeid
54  WHERE
55    buildbot.changes.when_timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY))
56    AND buildbot.changes.project = 'krzk-github';
57  
58  # All (so also krzk and mainline):
59  SELECT COUNT(*) FROM buildbot.changes
60  LEFT JOIN buildbot.sourcestamps ON buildbot.changes.sourcestampid = buildbot.sourcestamps.id
61  LEFT JOIN buildbot.change_files ON buildbot.changes.changeid = buildbot.change_files.changeid
62  WHERE
63    buildbot.changes.when_timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 360 DAY));
64  
65  DELETE buildbot.changes, buildbot.sourcestamps, buildbot.change_files FROM buildbot.changes
66  LEFT JOIN buildbot.sourcestamps ON buildbot.changes.sourcestampid = buildbot.sourcestamps.id
67  LEFT JOIN buildbot.change_files ON buildbot.changes.changeid = buildbot.change_files.changeid
68  WHERE
69    buildbot.changes.when_timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 360 DAY));
70  
71  # Cleanup after previous deletes:
72  DELETE buildbot.change_files FROM buildbot.change_files
73  LEFT JOIN buildbot.changes ON buildbot.changes.changeid = buildbot.change_files.changeid
74  WHERE buildbot.changes.changeid IS NULL;
75  
76  DELETE buildbot.sourcestamps FROM buildbot.sourcestamps
77  LEFT JOIN buildbot.changes ON buildbot.changes.sourcestampid = buildbot.sourcestamps.id
78  WHERE buildbot.changes.changeid IS NULL;
79  
80  OPTIMIZE TABLE buildbot.changes, buildbot.sourcestamps, buildbot.change_files;
81  
82  SELECT COUNT(*),project FROM buildbot.changes GROUP BY project;
83  
84  # Select pending build requests for specific project:
85     
86  SELECT buildbot.buildrequests.*, from_unixtime(buildbot.buildrequests.submitted_at), buildbot.buildset_sourcestamps.buildsetid, buildbot.sourcestamps.id, buildbot.sourcestamps.project FROM buildbot.buildrequests
87  INNER JOIN buildbot.buildset_sourcestamps ON buildbot.buildrequests.buildsetid = buildbot.buildset_sourcestamps.buildsetid
88  INNER JOIN buildbot.sourcestamps ON buildbot.buildset_sourcestamps.sourcestampid = buildbot.sourcestamps.id
89  WHERE buildbot.buildrequests.complete = 0
90  	AND buildbot.sourcestamps.project = "krzk";
91  # And remove them:
92  DELETE buildbot.buildrequests FROM buildbot.buildrequests
93  INNER JOIN buildbot.buildset_sourcestamps ON buildbot.buildrequests.buildsetid = buildbot.buildset_sourcestamps.buildsetid
94  INNER JOIN buildbot.sourcestamps ON buildbot.buildset_sourcestamps.sourcestampid = buildbot.sourcestamps.id
95  WHERE buildbot.buildrequests.complete = 0
96  	AND buildbot.sourcestamps.project = "krzk";