All that said, it might be better to use a separate table to obviate the need for formatting and parsing and make queries easier to write (with indices to speed up both directions -- find who's breaking a package, find which packages are broken by a package):
CREATE TABLE failed_deps (
dependent INTEGER REFERENCES results,
dependency INTEGER REFERENCES results,
PRIMARY KEY(dependent, dependency)
) WITHOUT ROWID;
CREATE INDEX failed_deps_i_dependent ON failed_deps (dependent);
CREATE INDEX failed_deps_i_dependency ON failed_deps (dependency);
Though this takes a little more coding to adapt bulktracker than simply adding the index, it would likely speed up the display of a package results details page of a failed package like https://releng.netbsd.org/bulktracker/pkg/17647093.
(It would also be more reliable than the current getPkgsBrokenBy query, whose LIKE test could be confused if, e.g., there were both py311-xyz-1.2.3 and xyz-1.2.3 packages in the build. I don't know if this happens now but I certainly can't rule it out.)
As suggested in https://github.com/bsiegert/BulkTracker/issues/56:
Though this takes a little more coding to adapt bulktracker than simply adding the index, it would likely speed up the display of a package results details page of a failed package like https://releng.netbsd.org/bulktracker/pkg/17647093.
(It would also be more reliable than the current getPkgsBrokenBy query, whose LIKE test could be confused if, e.g., there were both
py311-xyz-1.2.3andxyz-1.2.3packages in the build. I don't know if this happens now but I certainly can't rule it out.)