def _removeTrove(self, name, version, flavor, markOnly = False):
        cu = self.db.cursor()

        cu.execute("""
        SELECT I.instanceId, I.itemId, I.versionId, I.flavorId, I.troveType
        FROM Instances as I
        JOIN Items USING (itemId)
        JOIN Versions ON I.versionId = Versions.versionId
        JOIN Flavors ON I.flavorId = Flavors.flavorId
        WHERE Items.item = ?
          AND Versions.version = ?
          AND Flavors.flavor = ?
        """, (name, version.asString(), flavor.freeze()))

        try:
            instanceId, itemId, versionId, flavorId, troveType = cu.next()
        except StopIteration:
            raise errors.TroveMissing(name, version)

        if troveType == trove.TROVE_TYPE_REMOVED:
            # double removes are okay; they just get ignored
            return []

        assert(troveType == trove.TROVE_TYPE_NORMAL or
               troveType == trove.TROVE_TYPE_REDIRECT)

        # remove all dependencies which are used only by this instanceId
        cu.execute("""
        select prov.depId as depId from
        ( select a.depId as depId from
          ( select depId, instanceId from Provides where instanceId = :instanceId
            union
            select depId, instanceId  from Requires where instanceId = :instanceId
          ) as a
          left join Provides as p on a.depId = p.depId and p.instanceId != a.instanceId
          where p.depId is NULL
        ) as prov
        join
        ( select a.depId as depId from
          ( select depId, instanceId from Provides where instanceId = :instanceId
            union
            select depId, instanceId  from Requires where instanceId = :instanceId
          ) as a
          left join Requires as r on a.depId = r.depId and r.instanceId != a.instanceId
          where r.depId is NULL
        ) as reqs
        on prov.depId = reqs.depId
        """, instanceId = instanceId )
        depsToRemove = [ x[0] for x in cu ]

        cu.execute("DELETE FROM Provides WHERE instanceId = ?", instanceId)
        cu.execute("DELETE FROM Requires WHERE instanceId = ?", instanceId)

        if depsToRemove:
            cu.execute("DELETE FROM Dependencies WHERE depId IN (%s)"
                       % ",".join([ "%d" % x for x in depsToRemove ]))

        # Remove from TroveInfo
        cu.execute("DELETE FROM TroveInfo WHERE instanceId = ?", instanceId)

        # Look for path/pathId combinations we don't need anymore
        cu.execute("""
        select FilePaths.filePathId
        from TroveFiles as TF
        join FilePaths using(filePathId)
        where TF.instanceId = ?
          and not exists (
              select instanceId from TroveFiles as Others
              where Others.filePathId = TF.filePathId
                and Others.instanceId != ? )
        """, (instanceId, instanceId))

        filePathIdsToRemove = [ x[0] for x in cu ]

        # Now remove the files. Gather a list of sha1s of files to remove
        # from the filestore.
        cu.execute("""
        select FileStreams.streamId, FileStreams.sha1
        from TroveFiles as TF
        join FileStreams using(streamId)
        where TF.instanceId = ?
          and not exists (
              select instanceId from TroveFiles as Others
              where Others.streamId = TF.streamId
                and Others.instanceId != ? )
        """, (instanceId, instanceId))

        r = cu.fetchall()
        # if sha1 is None, the file has no contents
        candidateSha1sToRemove = [ cu.frombinary(x[1]) for x in r
                if x[1] is not None ]
        streamIdsToRemove = [ x[0] for x in r ]

        cu.execute("DELETE FROM TroveFiles WHERE instanceId = ?", instanceId)
        if streamIdsToRemove:
            cu.execute("DELETE FROM FileStreams WHERE streamId IN (%s)"
                       % ",".join("%d"%x for x in streamIdsToRemove))

        if filePathIdsToRemove:
            cu.execute("DELETE FROM FilePaths WHERE filePathId IN (%s)"
                       % ",".join("%d"%x for x in filePathIdsToRemove))
            # XXX: these cleanups are more expensive than they're worth, probably
            cu.execute(""" delete from Dirnames where not exists (
                select 1 from FilePaths as fp where fp.dirnameId = Dirnames.dirnameId )
                """)
            cu.execute(""" delete from Basenames where not exists (
                select 1 from FilePaths as fp where fp.basenameId = Basenames.basenameId ) """)

        # we need to double check filesToRemove against other streams which
        # may need the same sha1
        filesToRemove = []
        for sha1 in candidateSha1sToRemove:
            cu.execute("SELECT COUNT(*) FROM FileStreams WHERE sha1=?", cu.binary(sha1))
            if cu.next()[0] == 0:
                filesToRemove.append(sha1)

        # tmpRemovals drives the removal of most of the shared tables
        schema.resetTable(cu, 'tmpRemovals')
        cu.execute("SELECT nodeId, branchId FROM Nodes "
                   "WHERE itemId = ? AND versionId = ?", (itemId, versionId))
        nodeId, branchId = cu.next()
        cu.execute("INSERT INTO tmpRemovals (itemId, versionId, flavorId, branchId) "
                   "VALUES (?, ?, ?, ?)", (itemId, versionId, flavorId, branchId),
                   start_transaction=False)
        # Look for troves which this trove references which aren't present
        # on this repository (if they are present, we shouldn't remove them)
        # and aren't referenced by anything else
        cu.execute("""
        INSERT INTO tmpRemovals (instanceId, itemId, versionId, flavorId, branchId)
        SELECT Instances.instanceId, Instances.itemId, Instances.versionId,
               Instances.flavorId, Nodes.branchId
        FROM TroveTroves
        JOIN Instances ON TroveTroves.includedId = Instances.instanceId
        JOIN Nodes ON
            Instances.itemId = Nodes.itemId AND
            Instances.versionId = Nodes.versionId
        WHERE
         TroveTroves.instanceId = ?
         and Instances.isPresent = ?
         and not exists (select instanceId from TroveTroves as Others
                          where Others.instanceId != ?
                            and Others.includedId = Instances.instanceId )
        """, (instanceId, instances.INSTANCE_PRESENT_MISSING, instanceId),
                   start_transaction=False)
        cu.execute("""
        INSERT INTO tmpRemovals (itemId, flavorId, branchId)
        SELECT TroveRedirects.itemId, TroveRedirects.flavorId,
               TroveRedirects.branchId
        FROM TroveRedirects WHERE TroveRedirects.instanceId = ?
        """, instanceId, start_transaction=False)
        self.db.analyze("tmpRemovals")

        # remove access to troves we're about to remove
        self.ri.deleteInstanceIds("tmpRemovals")
        cu.execute("DELETE FROM TroveTroves WHERE instanceId=?", instanceId)
        cu.execute("DELETE FROM TroveRedirects WHERE instanceId=?", instanceId)
        cu.execute("DELETE FROM Instances WHERE instanceId IN "
                        "(SELECT instanceId FROM tmpRemovals)")
        if markOnly:
            # We don't actually remove anything here; we just mark the trove
            # as removed instead
            cu.execute("UPDATE Instances SET troveType=? WHERE instanceId=?",
                       trove.TROVE_TYPE_REMOVED, instanceId)
            self.latest.update(cu, itemId, branchId, flavorId)
        else:
            self.ri.deleteInstanceId(instanceId)
            cu.execute("DELETE FROM Instances WHERE instanceId = ?", instanceId)

        # look for troves referenced by this one
        schema.resetTable(cu, 'tmpId')
        cu.execute("""
        INSERT INTO tmpId(id)
        SELECT Nodes.nodeId
        FROM tmpRemovals
        JOIN Nodes USING (itemId, versionId)
        LEFT JOIN Instances USING (itemId, versionId)
        WHERE Instances.itemId IS NULL
        """, start_transaction=False)
        self.db.analyze("tmpId")

        # Was this the only Instance for the node?
        cu.execute("""
        DELETE FROM Changelogs
        WHERE Changelogs.nodeId IN (SELECT id FROM tmpId)
        """)

        cu.execute("""
        DELETE FROM Nodes
        WHERE Nodes.nodeId IN (SELECT id FROM tmpId)
        """)

        # Now update the latest table
        self.latest.update(cu, itemId, branchId, flavorId)

        # Delete flavors which are no longer needed
        cu.execute("""
        delete from Flavors
        where flavorId in (
            select r.flavorId
            from tmpRemovals as r
            where not exists (select flavorId from Instances as i where i.flavorId = r.flavorId)
            and not exists (select flavorId from TroveRedirects as tr where tr.flavorId = r.flavorId)
        )""")

        cu.execute("""
        delete from FlavorMap
        where flavorId in (
            select r.flavorId
            from tmpRemovals as r
            where not exists (select flavorId from Flavors as f where f.flavorId = r.flavorId)
        )""")

        # do we need the labelmap entry anymore?
        schema.resetTable(cu, 'tmpId')
        cu.execute("""insert into tmpId(id)
        select lm.labelmapId
        from tmpRemovals as r
        join LabelMap as lm using(itemId, branchId)
        where not exists ( select nodeId from Nodes as n
                           where n.itemId = lm.itemId
                           and n.branchId = lm.branchId ) """, start_transaction=False)
        self.db.analyze("tmpId")
        cu.execute("delete from LabelMap where labelmapId in (select id from tmpId)")

        # do we need these branchIds anymore?
        cu.execute("""
        delete from Branches
        where branchId in (
            select r.branchId
            from tmpRemovals as r
            where not exists (select branchId from Nodes as n where n.branchId = r.branchId)
              and not exists (select branchId from TroveRedirects as tr where tr.branchId = r.branchId)
        )""")

        # XXX It would be nice to narrow this down based on tmpRemovals, but
        # in reality the labels table never gets that big.
        schema.resetTable(cu, 'tmpId')
        cu.execute("""insert into tmpId(id)
        select l.labelId
        from Labels as l
        where not exists (select labelId from LabelMap as lm where lm.labelId = l.labelId)
        and not exists (select labelId from Permissions as p where p.labelId = l.labelId)
        and l.labelId != 0 """, start_transaction=False)
        self.db.analyze("tmpId")
        cu.execute("delete from Labels where labelId in (select id from tmpId)")

        # clean up Versions
        cu.execute("""
        delete from Versions
        where versionId in (
            select r.versionId
            from tmpRemovals as r
            where not exists (select versionId from Instances as i where i.versionId = r.versionId)
            and not exists (select versionId from Nodes as n where n.versionId = r.versionId)
            and not exists (select versionId from TroveFiles as tf where tf.versionId = r.versionId)
        )""")

        # clean up Items
        cu.execute("""
        delete from Items
        where itemId in (
            select r.itemId
            from tmpRemovals as r
            where not exists (select itemId from Instances as i where i.itemId = r.itemId)
            and not exists (select itemId from Nodes as n where n.itemId = r.itemId)
            and not exists (select itemId from TroveRedirects as tr where tr.itemId = r.itemId)
            and not exists (select itemId from Permissions as p where p.itemId = r.itemId)
        )""")

        # XXX what about metadata?
        return filesToRemove
dataset/ETHPy150Open sassoftware/conary/conary/repository/netrepos/trovestore.py/TroveStore._removeTrove/original