1""" 

2helper functions for cruft-report 

3 

4@contact: Debian FTPMaster <ftpmaster@debian.org> 

5@copyright 2011 Torsten Werner <twerner@debian.org> 

6""" 

7 

8# This program is free software; you can redistribute it and/or modify 

9# it under the terms of the GNU General Public License as published by 

10# the Free Software Foundation; either version 2 of the License, or 

11# (at your option) any later version. 

12 

13# This program is distributed in the hope that it will be useful, 

14# but WITHOUT ANY WARRANTY; without even the implied warranty of 

15# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 

16# GNU General Public License for more details. 

17 

18# You should have received a copy of the GNU General Public License 

19# along with this program; if not, write to the Free Software 

20# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA 

21 

22################################################################################ 

23 

24from sqlalchemy import func 

25from sqlalchemy.orm import aliased, object_session 

26 

27from daklib.dbconn import Architecture, DBBinary, DBSource, Suite, get_suite 

28 

29 

30def newer_version( 

31 lowersuite_name: str, highersuite_name: str, session, include_equal=False 

32) -> list[tuple[str, str, str]]: 

33 """ 

34 Finds newer versions in lowersuite_name than in highersuite_name. Returns a 

35 list of tuples (source, higherversion, lowerversion) where higherversion is 

36 the newest version from highersuite_name and lowerversion is the newest 

37 version from lowersuite_name. 

38 """ 

39 

40 lowersuite = get_suite(lowersuite_name, session) 

41 highersuite = get_suite(highersuite_name, session) 

42 

43 def get_suite_sources(suite): 

44 q1 = ( 

45 session.query(DBSource.source, func.max(DBSource.version).label("version")) 

46 .with_parent(suite) 

47 .group_by(DBSource.source) 

48 .subquery() 

49 ) 

50 

51 return aliased(q1) 

52 

53 def get_suite_binaries(suite): 

54 q1 = ( 

55 session.query( 

56 DBBinary.package, 

57 DBSource.source, 

58 func.max(DBSource.version).label("version"), 

59 Architecture.arch_string, 

60 func.max(DBBinary.version).label("binversion"), 

61 ) 

62 .filter(DBBinary.suites.contains(suite)) 

63 .join(DBBinary.source) 

64 .join(DBBinary.architecture) 

65 .group_by( 

66 DBBinary.package, 

67 DBSource.source, 

68 Architecture.arch_string, 

69 ) 

70 .subquery() 

71 ) 

72 

73 return aliased(q1) 

74 

75 highq = get_suite_sources(highersuite) 

76 lowq = get_suite_sources(lowersuite) 

77 

78 query = session.query( 

79 highq.c.source, 

80 highq.c.version.label("higherversion"), 

81 lowq.c.version.label("lowerversion"), 

82 ).join(lowq, highq.c.source == lowq.c.source) 

83 

84 if include_equal: 

85 query = query.filter(highq.c.version <= lowq.c.version) 

86 else: 

87 query = query.filter(highq.c.version < lowq.c.version) 

88 

89 list = [] 

90 # get all sources that have a higher version in lowersuite than in 

91 # highersuite 

92 for source, higherversion, lowerversion in query: 

93 q1 = ( 

94 session.query( 

95 DBBinary.package, 

96 DBSource.source, 

97 DBSource.version, 

98 Architecture.arch_string, 

99 ) 

100 .filter(DBBinary.suites.contains(highersuite)) 

101 .join(DBBinary.source) 

102 .join(DBBinary.architecture) 

103 .filter(DBSource.source == source) 

104 .subquery() 

105 ) 

106 q2 = session.query(q1.c.arch_string).group_by(q1.c.arch_string) 

107 # all architectures for which source has binaries in highersuite 

108 archs_high = set(x[0] for x in q2.all()) 

109 

110 highq = get_suite_binaries(highersuite) 

111 lowq = get_suite_binaries(lowersuite) 

112 

113 query = ( 

114 session.query(highq.c.arch_string) 

115 .join(lowq, highq.c.source == lowq.c.source) 

116 .filter(highq.c.arch_string == lowq.c.arch_string) 

117 .filter(highq.c.package == lowq.c.package) 

118 .filter(highq.c.source == source) 

119 ) 

120 

121 if include_equal: 

122 query = query.filter(highq.c.binversion <= lowq.c.binversion).filter( 

123 highq.c.version <= lowq.c.version 

124 ) 

125 else: 

126 query = query.filter(highq.c.binversion < lowq.c.binversion).filter( 

127 highq.c.version < lowq.c.version 

128 ) 

129 

130 query = query.group_by(highq.c.arch_string) 

131 

132 # all architectures for which source has a newer binary in lowersuite 

133 archs_newer = set(x[0] for x in query.all()) 

134 

135 # if has at least one binary in lowersuite which is newer than the one 

136 # in highersuite on each architecture for which source has binaries in 

137 # highersuite, we know that the builds for all relevant architecture 

138 # are done, so we can remove the old source with it's binaries 

139 if archs_newer >= archs_high: 

140 list.append((source, higherversion, lowerversion)) 

141 

142 list.sort() 

143 return list 

144 

145 

146def get_package_names(suite: Suite): 

147 """ 

148 Returns a query that selects all distinct package names from suite ordered 

149 by package name. 

150 """ 

151 

152 session = object_session(suite) 

153 return ( 

154 session.query(DBBinary.package) 

155 .with_parent(suite) 

156 .group_by(DBBinary.package) 

157 .order_by(DBBinary.package) 

158 ) 

159 

160 

161class NamedSource: 

162 """ 

163 A source package identified by its name with all of its versions in a 

164 suite. 

165 """ 

166 

167 def __init__(self, suite: Suite, source: str): 

168 self.source = source 

169 query = suite.sources.filter_by(source=source).order_by(DBSource.version) 

170 self.versions = [src.version for src in query] 

171 

172 def __str__(self): 

173 return "%s(%s)" % (self.source, ", ".join(self.versions)) 

174 

175 

176class DejavuBinary: 

177 """ 

178 A binary package identified by its name which gets built by multiple source 

179 packages in a suite. The architecture is ignored which leads to the 

180 following corner case, e.g.: 

181 

182 If a source package 'foo-mips' that builds a binary package 'foo' on mips 

183 and another source package 'foo-mipsel' builds a binary package with the 

184 same name 'foo' on mipsel then the binary package 'foo' will be reported as 

185 built from multiple source packages. 

186 """ 

187 

188 def __init__(self, suite: Suite, package: str): 

189 self.package = package 

190 session = object_session(suite) 

191 # We need a subquery to make sure that both binary and source packages 

192 # are in the right suite. 

193 bin_query = suite.binaries.filter_by(package=package).subquery() 

194 src_query = ( 

195 session.query(DBSource.source) 

196 .with_parent(suite) 

197 .join(bin_query) 

198 .order_by(DBSource.source) 

199 .group_by(DBSource.source) 

200 ) 

201 self.sources = [] 

202 if src_query.count() > 1: 

203 for (source,) in src_query: 

204 self.sources.append(str(NamedSource(suite, source))) 

205 

206 def has_multiple_sources(self) -> bool: 

207 "Has the package been built by multiple sources?" 

208 return len(self.sources) > 1 

209 

210 def __str__(self): 

211 return "%s built by: %s" % (self.package, ", ".join(self.sources)) 

212 

213 

214def report_multiple_source(suite: Suite) -> None: 

215 """ 

216 Reports binary packages built from multiple source package with different 

217 names. 

218 """ 

219 

220 print("Built from multiple source packages") 

221 print("-----------------------------------") 

222 print() 

223 for (package,) in get_package_names(suite): 

224 binary = DejavuBinary(suite, package) 

225 if binary.has_multiple_sources(): 

226 print(binary) 

227 print() 

228 

229 

230def query_without_source(suite_id: int, session): 

231 """searches for arch: all packages from suite that do no longer 

232 reference a source package in the same suite 

233 

234 subquery unique_binaries: selects all packages with only 1 version 

235 in suite since 'dak rm' does not allow to specify version numbers""" 

236 

237 query = """ 

238 with unique_binaries as 

239 (select package, max(version) as version, max(source) as source 

240 from bin_associations_binaries 

241 where architecture = 2 and suite = :suite_id 

242 group by package having count(*) = 1) 

243 select ub.package, ub.version 

244 from unique_binaries ub 

245 left join src_associations_src sas 

246 on ub.source = sas.src and sas.suite = :suite_id 

247 where sas.id is null 

248 order by ub.package""" 

249 return session.execute(query, {"suite_id": suite_id}) 

250 

251 

252def queryNBS(suite_id: int, session): 

253 """This one is really complex. It searches arch != all packages that 

254 are no longer built from current source packages in suite. 

255 

256 temp table unique_binaries: will be populated with packages that 

257 have only one version in suite because 'dak rm' does not allow 

258 specifying version numbers 

259 

260 temp table newest_binaries: will be populated with packages that are 

261 built from current sources 

262 

263 subquery uptodate_arch: returns all architectures built from current 

264 sources 

265 

266 subquery unique_binaries_uptodate_arch: returns all packages in 

267 architectures from uptodate_arch 

268 

269 subquery unique_binaries_uptodate_arch_agg: same as 

270 unique_binaries_uptodate_arch but with column architecture 

271 aggregated to array 

272 

273 subquery uptodate_packages: similar to uptodate_arch but returns all 

274 packages built from current sources 

275 

276 subquery outdated_packages: returns all packages with architectures 

277 no longer built from current source 

278 """ 

279 

280 query = """ 

281with 

282 unique_binaries as 

283 (select 

284 bab.package, 

285 bab.architecture, 

286 max(bab.source) as source 

287 from bin_associations_binaries bab 

288 where bab.suite = :suite_id and bab.architecture > 2 

289 group by package, architecture having count(*) = 1), 

290 newest_binaries as 

291 (select ub.package, ub.architecture, nsa.source, nsa.version 

292 from unique_binaries ub 

293 join newest_src_association nsa 

294 on ub.source = nsa.src and nsa.suite = :suite_id), 

295 uptodate_arch as 

296 (select architecture, source, version 

297 from newest_binaries 

298 group by architecture, source, version), 

299 unique_binaries_uptodate_arch as 

300 (select ub.package, (select a.arch_string from architecture a where a.id = ub.architecture) as arch_string, ua.source, ua.version 

301 from unique_binaries ub 

302 join source s 

303 on ub.source = s.id 

304 join uptodate_arch ua 

305 on ub.architecture = ua.architecture and s.source = ua.source), 

306 unique_binaries_uptodate_arch_agg as 

307 (select ubua.package, 

308 array_agg(ubua.arch_string order by ubua.arch_string) as arch_list, 

309 ubua.source, ubua.version 

310 from unique_binaries_uptodate_arch ubua 

311 group by ubua.source, ubua.version, ubua.package), 

312 uptodate_packages as 

313 (select package, source, version 

314 from newest_binaries 

315 group by package, source, version), 

316 outdated_packages as 

317 (select array_agg(package order by package) as pkg_list, 

318 arch_list, source, version 

319 from unique_binaries_uptodate_arch_agg 

320 where package not in 

321 (select package from uptodate_packages) 

322 group by arch_list, source, version) 

323 select * from outdated_packages order by source""" 

324 return session.execute(query, {"suite_id": suite_id}) 

325 

326 

327def queryNBS_metadata(suite_id: int, session): 

328 """searches for NBS packages based on metadata extraction of the 

329 newest source for a given suite""" 

330 

331 query = """ 

332 select string_agg(bin.package, ' ' order by bin.package), ( 

333 select arch_string 

334 from architecture 

335 where id = bin.architecture) as architecture, src.source, newsrc.version 

336 from bin_associations_binaries bin 

337 join src_associations_src src 

338 on src.src = bin.source 

339 and src.suite = bin.suite 

340 join newest_src_association newsrc 

341 on newsrc.source = src.source 

342 and newsrc.version != src.version 

343 and newsrc.suite = bin.suite 

344 where bin.suite = :suite_id 

345 and bin.package not in ( 

346 select trim(' \n' from unnest(string_to_array(meta.value, ','))) 

347 from source_metadata meta 

348 where meta.src_id = ( 

349 select newsrc.src 

350 from newest_src_association newsrc 

351 where newsrc.source = ( 

352 select s.source 

353 from source s 

354 where s.id = bin.source) 

355 and newsrc.suite = bin.suite) 

356 and key_id = ( 

357 select key_id 

358 from metadata_keys 

359 where key = 'Binary')) 

360 group by src.source, newsrc.version, architecture 

361 order by src.source, newsrc.version, bin.architecture""" 

362 return session.execute(query, {"suite_id": suite_id})