Clean build and changes in db

Signed-off-by: Magnus Granberg <zorry@gentoo.org>

3 files changed, 228 insertions, 104 deletions

diff --git a/buildbot_gentoo_ci/steps/clean.py b/buildbot_gentoo_ci/steps/clean.py
index 73733f1..e4d8573 100644
--- a/buildbot_gentoo_ci/steps/clean.py
+++ b/buildbot_gentoo_ci/steps/clean.py
@@ -31,6 +31,7 @@ class SetupPropertys(BuildStep):
         days = 30
         days_in_s = 86400 * days
         now = int(self.master.reactor.seconds())
+        bb_build_ids = []
         for ebuild_data in deleted_ebuilds_data:
             no_bug = True
             # check if older the days
@@ -41,13 +42,20 @@ class SetupPropertys(BuildStep):
                 print(build_data_list)
                 for build_data in build_data_list:
                     if build_data['bug_id'] == 0:
-                        # get buildsets
                         print(f"Build : {build_data['id']} will be removed")
-                        yield self.build.addStepsAfterCurrentStep([RemoveBuildFromDb(build_data)])
+                        if build_data['buildbot_build_id'] != 0:
+                            bb_build_ids.append(build_data['buildbot_build_id'])
+                            # make a list with build_ids from parent_buildid
+                            # get parent_buildid from buildsets
+                            # bb_buildset = yield self.master.db.buildsets.getBuildset(build_data['buildbot_build_id'])
+                        yield self.gentooci.db.builds.removeBuild(build_data['id'])
                     else:
                         no_bug = False
                 if no_bug:
-                    yield self.build. addStepsAfterLastStep([RemoveEbuildFromDb(ebuild_data)])
+                    yield self.build.addStepsAfterCurrentStep([RemoveEbuildFromDb(ebuild_data['uuid'])])
+                    yield self.build.addStepsAfterCurrentStep([RemoveChangesFromDb(ebuild_data['commit_id'])])
+        for bb_build_id in bb_build_ids:
+            yield self.build.addStepsAfterCurrentStep([RemoveBuildFromDb(bb_build_id)])
         return SUCCESS
 
 class RemoveEbuildFromDb(BuildStep):
@@ -57,17 +65,34 @@ class RemoveEbuildFromDb(BuildStep):
     haltOnFailure = True
     flunkOnFailure = True
 
-    def __init__(self, ebuild_data, **kwargs):
-        self.ebuild_data = ebuild_data
+    def __init__(self, uuid, **kwargs):
+        self.uuid = uuid
         super().__init__(**kwargs)
 
     @defer.inlineCallbacks
     def run(self):
         self.gentooci = self.master.namedServices['services'].namedServices['gentooci']
-        yield self.gentooci.db.versions.removeVersionKeyword(self.ebuild_data['uuid'])
-        yield self.gentooci.db.versions.removeVersionMetadata(self.ebuild_data['uuid'])
-        yield self.gentooci.db.versions.removeVersion(self.ebuild_data['uuid'])
-        self.descriptionDone = f"Ebuild : {self.ebuild_data['uuid']} will be removed"
+        yield self.gentooci.db.versions.removeVersionKeyword(self.uuid)
+        yield self.gentooci.db.versions.removeVersionMetadata(self.uuid)
+        yield self.gentooci.db.versions.removeVersion(self.uuid)
+        self.descriptionDone = f"Ebuild : {self.uuid} will be removed"
+        return SUCCESS
+
+class RemoveChangesFromDb(BuildStep):
+    name = 'Clean Changes from db'
+    description = 'Running'
+    descriptionSuffix = None
+    haltOnFailure = True
+    flunkOnFailure = True
+
+    def __init__(self, commit_id, **kwargs):
+        self.commit_id = commit_id
+        super().__init__(**kwargs)
+
+    @defer.inlineCallbacks
+    def run(self):
+        yield self.master.db.changes.pruneChangesId(self.commit_id)
+        self.descriptionDone = f"Change : {self.commit_id} will be removed"
         return SUCCESS
 
 class RemoveBuildFromDb(BuildStep):
@@ -77,21 +102,24 @@ class RemoveBuildFromDb(BuildStep):
     haltOnFailure = True
     flunkOnFailure = True
 
-    def __init__(self, build_data, **kwargs):
-        self.build_data = build_data
+    def __init__(self, build_id, **kwargs):
+        self.build_id = build_id
         super().__init__(**kwargs)
 
     @defer.inlineCallbacks
     def run(self):
         self.gentooci = self.master.namedServices['services'].namedServices['gentooci']
-        if self.build_data['buildbot_build_id'] != 0:
-            self.descriptionDone = f"BuildBot Build : {self.build_data['buildbot_build_id']} will be removed"
-            bb_build_data = yield self.master.db.builds.getBuild(self.build_data['buildbot_build_id'])
-            # remove buildbot data on db
-            # remove steps and logs
-            yield self.master.db.logs.deleteLogChunks(self.build_data['buildbot_build_id'])
-            # remove propertys
-            # get buildsets
-            # remove build_data
-        yield self.gentooci.db.builds.removeBuild(self.build_data['id'])
+        self.descriptionDone = f"BuildBot Build : {self.build_id} will be removed"
+        bb_build_data = yield self.master.db.builds.getBuild(self.build_id)
+        # remove steps and logs
+        yield self.master.db.logs.deleteLogChunks(self.build_id)
+        # remove propertys
+        yield self.master.db.builds.pruneBuildProperties(self.build_id)
+        # remove buildset_sourcestamps
+        # remove buildrequests/buildrequest_claims
+        # remove buildsets/buildset_properties
+        # remove build_data
+        yield self.master.db.build_data.pruneBuildData(self.build_id)
+        # remove build
+        yield self.master.db.builds.pruneBuild(self.build_id)
         return SUCCESS
diff --git a/patches/bb-clean-db.patch b/patches/bb-clean-db.patch
new file mode 100644
index 0000000..8d370a4
--- /dev/null
+++ b/patches/bb-clean-db.patch
@@ -0,0 +1,179 @@
+--- a/buildbot/db/build_data.py	2023-09-19 23:24:04.714268581 +0200
++++ b/buildbot/db/build_data.py	2023-09-24 22:28:21.778583084 +0200
+@@ -159,6 +159,15 @@ class BuildDataConnectorComponent(base.D
+         res = yield self.db.pool.do(thd)
+         return res
+ 
++    @defer.inlineCallbacks
++    def pruneBuildData(self, buildid):
++        def thd(conn):
++            tbl = self.db.model.build_data
++            q = tbl.delete()
++            q = q.where(tbl.c.buildid == buildid)
++            conn.execute(q)
++        yield self.db.pool.do(thd)
++
+     def _row2dict(self, conn, row):
+         return BuildDataDict(buildid=row.buildid,
+                              name=row.name,
+--- a/buildbot/db/builds.py	2023-09-19 23:24:04.714268581 +0200
++++ b/buildbot/db/builds.py	2023-09-24 15:31:20.598023751 +0200
+@@ -206,6 +206,15 @@ class BuildsConnectorComponent(base.DBCo
+                          results=results)
+         return self.db.pool.do(thd)
+ 
++    @defer.inlineCallbacks
++    def pruneBuild(self, buildid):
++        def thd(conn):
++            tbl = self.db.model.builds
++            q = tbl.delete()
++            q = q.where(tbl.c.id == buildid)
++            conn.execute(q)
++        yield self.db.pool.do(thd)
++
+     # returns a Deferred that returns a value
+     def getBuildProperties(self, bid, resultSpec=None):
+         def thd(conn):
+@@ -251,6 +260,15 @@ class BuildsConnectorComponent(base.DBCo
+                              {"value": value_js, "source": source})
+         yield self.db.pool.do(thd)
+ 
++    @defer.inlineCallbacks
++    def pruneBuildProperties(self, buildid):
++        def thd(conn):
++            bp_tbl = self.db.model.build_properties
++            q = bp_tbl.delete()
++            q = q.where(bp_tbl.c.buildid == buildid)
++            conn.execute(q)
++        yield self.db.pool.do(thd)
++
+     def _builddictFromRow(self, row):
+         return {
+             "id": row.id,
+--- a/buildbot/db/changes.py	2023-09-19 23:24:04.714268581 +0200
++++ b/buildbot/db/changes.py	2023-09-24 13:53:28.166432187 +0200
+@@ -333,6 +334,40 @@ class ChangesConnectorComponent(base.DBC
+                         table.delete(table.c.changeid.in_(batch)))
+         yield self.db.pool.do(thd)
+ 
++    @defer.inlineCallbacks
++    def pruneChangesId(self, revision):
++        """
++        Called periodically by DBConnector, this method deletes changes
++        than C{changeHorizon}.
++        """
++
++        def thd(conn):
++            changes_tbl = self.db.model.changes
++            ids_to_delete = []
++            # First, get the list of changes to delete.  This could be written
++            # as a subquery but then that subquery would be run for every
++            # table, which is very inefficient; also, MySQL's subquery support
++            # leaves much to be desired, and doesn't support this particular
++            # form.
++            q = changes_tbl.select()
++            q = q.where(changes_tbl.c.revision == revision)
++            res = conn.execute(q)
++            row = res.fetchone()
++            if row is not None:
++                ids_to_delete.append(row.changeid)
++
++            # and delete from all relevant tables, in dependency order
++            for table_name in ('scheduler_changes', 'change_files',
++                               'change_properties', 'changes', 'change_users'):
++                remaining = ids_to_delete[:]
++                while remaining:
++                    batch, remaining = remaining[:100], remaining[100:]
++                    table = self.db.model.metadata.tables[table_name]
++                    conn.execute(
++                        table.delete(table.c.changeid.in_(batch)))
++        yield self.db.pool.do(thd)
++
++
+     def _chdict_from_change_row_thd(self, conn, ch_row):
+         # This method must be run in a db.pool thread, and returns a chdict
+         # given a row from the 'changes' table
+
+--- a/buildbot/db/logs.py	2022-04-02 11:10:34.892310594 +0200
++++ b/buildbot/db/logs.py	2023-06-26 23:06:24.611959431 +0200
+@@ -410,3 +410,80 @@
+         rv = dict(row)
+         rv['complete'] = bool(rv['complete'])
+         return rv
++
++    # returns a Deferred that returns a value
++    def deleteLogChunks(self, buildid):
++        model = self.db.model
++        horizon_per_builder = False
++
++        def countLogchunks(conn):
++            res = conn.execute(sa.select([sa.func.count(model.logchunks.c.logid)]))
++            count = res.fetchone()[0]
++            res.close()
++            return count
++
++        # find the steps.id at the upper bound of steps
++        def getStepidMax(conn, buildid):
++            # N.B.: we utilize the fact that steps.id is auto-increment, thus steps.started_at
++            # times are effectively sorted and we only need to find the steps.id at the upper
++            # bound of steps to update.
++
++            # SELECT steps.id from steps WHERE steps.buildid = buildid ORDER BY
++            # steps.id DESC LIMIT 1;
++            res = conn.execute(
++                sa.select([model.steps.c.id])
++                .where(model.steps.c.buildid == buildid)
++                .order_by(model.steps.c.id.desc())
++                .limit(1)
++            )
++            res_list = res.fetchone()
++            stepid_max = None
++            if res_list:
++                stepid_max = res_list[0]
++            res.close()
++            return stepid_max
++
++        # query all logs with type 'd' and delete their chunks.
++        def deleteLogsWithTypeD(conn):
++            if self.db._engine.dialect.name == 'sqlite':
++                # sqlite does not support delete with a join, so for this case we use a subquery,
++                # which is much slower
++                q = sa.select([model.logs.c.id])
++                q = q.select_from(model.logs)
++                q = q.where(model.logs.c.type == 'd')
++
++                # delete their logchunks
++                q = model.logchunks.delete().where(model.logchunks.c.logid.in_(q))
++            else:
++                q = model.logchunks.delete()
++                q = q.where(model.logs.c.id == model.logchunks.c.logid)
++                q = q.where(model.logs.c.type == 'd')
++
++            res = conn.execute(q)
++            res.close()
++
++        def thddeleteLogs(conn):
++            count_before = countLogchunks(conn)
++
++            # update log types that match buildid
++            # we do it first to avoid having UI discrepancy
++
++            stepid_max = getStepidMax(conn, buildid)
++            if stepid_max:
++                # UPDATE logs SET logs.type = 'd'
++                # WHERE logs.stepid <= stepid_max AND type != 'd';
++                res = conn.execute(
++                    model.logs.update()
++                    .where(sa.and_(model.logs.c.stepid <= stepid_max,
++                                   model.logs.c.type != 'd'))
++                    .values(type='d')
++                )
++                res.close()
++
++            deleteLogsWithTypeD(conn)
++
++            count_after = countLogchunks(conn)
++            count = count_before - count_after
++
++            return count if count > 0 else 0
++        return self.db.pool.do(thddeleteLogs)
diff --git a/patches/bb-deleteLogChunks.patch b/patches/bb-deleteLogChunks.patch
deleted file mode 100644
index 6de332e..0000000
--- a/patches/bb-deleteLogChunks.patch
+++ /dev/null
@@ -1,83 +0,0 @@
---- a/buildbot/db/logs.py	2022-04-02 11:10:34.892310594 +0200
-+++ b/buildbot/db/logs.py	2023-06-26 23:06:24.611959431 +0200
-@@ -410,3 +410,80 @@
-         rv = dict(row)
-         rv['complete'] = bool(rv['complete'])
-         return rv
-+
-+    # returns a Deferred that returns a value
-+    def deleteLogChunks(self, buildid):
-+        model = self.db.model
-+        horizon_per_builder = False
-+
-+        def countLogchunks(conn):
-+            res = conn.execute(sa.select([sa.func.count(model.logchunks.c.logid)]))
-+            count = res.fetchone()[0]
-+            res.close()
-+            return count
-+
-+        # find the steps.id at the upper bound of steps
-+        def getStepidMax(conn, buildid):
-+            # N.B.: we utilize the fact that steps.id is auto-increment, thus steps.started_at
-+            # times are effectively sorted and we only need to find the steps.id at the upper
-+            # bound of steps to update.
-+
-+            # SELECT steps.id from steps WHERE steps.buildid = buildid ORDER BY
-+            # steps.id DESC LIMIT 1;
-+            res = conn.execute(
-+                sa.select([model.steps.c.id])
-+                .where(model.steps.c.buildid == buildid)
-+                .order_by(model.steps.c.id.desc())
-+                .limit(1)
-+            )
-+            res_list = res.fetchone()
-+            stepid_max = None
-+            if res_list:
-+                stepid_max = res_list[0]
-+            res.close()
-+            return stepid_max
-+
-+        # query all logs with type 'd' and delete their chunks.
-+        def deleteLogsWithTypeD(conn):
-+            if self.db._engine.dialect.name == 'sqlite':
-+                # sqlite does not support delete with a join, so for this case we use a subquery,
-+                # which is much slower
-+                q = sa.select([model.logs.c.id])
-+                q = q.select_from(model.logs)
-+                q = q.where(model.logs.c.type == 'd')
-+
-+                # delete their logchunks
-+                q = model.logchunks.delete().where(model.logchunks.c.logid.in_(q))
-+            else:
-+                q = model.logchunks.delete()
-+                q = q.where(model.logs.c.id == model.logchunks.c.logid)
-+                q = q.where(model.logs.c.type == 'd')
-+
-+            res = conn.execute(q)
-+            res.close()
-+
-+        def thddeleteLogs(conn):
-+            count_before = countLogchunks(conn)
-+
-+            # update log types that match buildid
-+            # we do it first to avoid having UI discrepancy
-+
-+            stepid_max = getStepidMax(conn, buildid)
-+            if stepid_max:
-+                # UPDATE logs SET logs.type = 'd'
-+                # WHERE logs.stepid <= stepid_max AND type != 'd';
-+                res = conn.execute(
-+                    model.logs.update()
-+                    .where(sa.and_(model.logs.c.stepid <= stepid_max,
-+                                   model.logs.c.type != 'd'))
-+                    .values(type='d')
-+                )
-+                res.close()
-+
-+            deleteLogsWithTypeD(conn)
-+
-+            count_after = countLogchunks(conn)
-+            count = count_before - count_after
-+
-+            return count if count > 0 else 0
-+        return self.db.pool.do(thddeleteLogs)