add a separate tool for generating hints on Multi-Arch headers

It builds on the core functionality of dedup, but uses a different
database schema. Unlike dedup, it aborts downloading Arch:all packages
early and consumes any other architecture in its entirety instead.

1 files changed, 199 insertions, 0 deletions

diff --git a/multiarchanalyze.sql b/multiarchanalyze.sql
new file mode 100644
index 0000000..6b26ecd
--- /dev/null
+++ b/multiarchanalyze.sql
@@ -0,0 +1,199 @@
+CREATE INDEX IF NOT EXISTS depends_dependee_index ON depends(dependee);
+CREATE INDEX IF NOT EXISTS content_filename_index ON content(filename);
+
+BEGIN;
+
+/* Combinations of two packages and files with conflicting content. */
+DROP VIEW IF EXISTS contentconflict;
+CREATE VIEW contentconflict AS
+	SELECT p1.id AS pid1,
+		p2.id AS pid2,
+		p1.name AS name1,
+		p2.name AS name2,
+		p1.version AS version1,
+		p2.version AS version2,
+		p1.architecture AS architecture1,
+		p2.architecture AS architecture2,
+		p1.source AS source1,
+		p2.source AS source2,
+		p1.hasscripts AS hasscripts1,
+		p2.hasscripts AS hasscripts2,
+		p1.multiarch AS multiarch1,
+		p2.multiarch AS multiarch2,
+		c1.filename,
+		c1.id AS cid1,
+		c2.id AS cid2,
+		c1.hash AS hash1,
+		c2.hash AS hash2
+	FROM package AS p1, package AS p2, content AS c1, content AS c2
+	WHERE p1.id = c1.pid
+		AND p2.id = c2.pid
+		AND c1.filename = c2.filename
+		AND c1.hash != c2.hash;
+
+/* Candidates for satisfying architecture-enforcing dependencies considering
+ * provides. */
+DROP VIEW IF EXISTS archdepcandidate;
+CREATE VIEW archdepcandidate AS
+	SELECT p1.id AS dependerid, p2.id AS dependeeid
+	FROM package AS p1, depends AS d, package AS p2
+	WHERE p1.id = d.pid
+		AND d.dependee = p2.name
+		AND p2.multiarch IS NOT 'foreign'
+		AND (p2.multiarch IS NOT 'allowed' OR d.archqual IS NOT 'any')
+		AND (p1.architecture = p2.architecture
+			OR p1.architecture = 'all'
+			OR p2.architecture = 'all'
+			OR p2.architecture = d.archqual)
+	UNION
+	SELECT p1.id AS dependerid, p2.id AS dependeeid
+	FROM package AS p1, depends AS d, provides AS r, package AS p2
+	WHERE p1.id = d.pid
+		AND d.dependee = r.provided
+		AND r.pid = p2.id
+		AND p2.multiarch IS NOT 'foreign'
+		AND (p2.multiarch IS NOT 'allowed' OR d.archqual IS NOT 'any')
+		AND (p1.architecture = p2.architecture
+			OR p1.architecture = 'all'
+			OR p2.architecture = 'all'
+			OR p2.architecture = d.archqual);
+
+/* Describe properties of binary package names. */
+DROP VIEW IF EXISTS packageapi;
+CREATE VIEW packageapi AS
+	SELECT name,
+		count(*) AS instances,
+		count(CASE architecture WHEN 'all' THEN 1 ELSE NULL END)
+			AS hasindep,
+		CASE count(CASE architecture WHEN 'all' THEN NULL ELSE 1 END)
+			WHEN 0 THEN 0 ELSE 1 END
+			AS hasarchdep,
+		CASE min(source) WHEN max(source) THEN source ELSE NULL END
+			AS source,
+		max(hasscripts) AS hasscripts,
+		CASE min(multiarch)
+			WHEN max(multiarch) THEN multiarch
+			ELSE NULL END
+			AS multiarch
+	FROM package GROUP BY name;
+
+/* Architecture-dependent packages. */
+DROP VIEW IF EXISTS archpackage;
+CREATE VIEW archpackage AS
+	SELECT
+		p1.name,
+		p1.hasscripts,
+	       	p1.multiarch,
+		exists(
+			SELECT 1 FROM package AS p2, archdepcandidate AS c
+			WHERE p2.name = p1.name AND c.dependerid = p2.id)
+			AS hasarchdeps
+	FROM packageapi AS p1
+	WHERE p1.instances >= 3 AND p1.hasindep = 0;
+
+/* Architecture-independent packages. */
+DROP VIEW IF EXISTS indeppackage;
+CREATE VIEW indeppackage AS
+	SELECT
+		id,
+		name,
+		version,
+		source,
+		hasscripts,
+		multiarch,
+		exists(SELECT 1 FROM archdepcandidate WHERE dependerid = id)
+			AS hasarchdeps
+	FROM package
+	WHERE architecture = 'all';
+
+/* Packages violating M-A:same by shipping conflicting files. */
+DROP VIEW IF EXISTS masame_conflict;
+CREATE VIEW masame_conflict AS
+	SELECT name1, architecture1, architecture2, filename
+	FROM contentconflict
+	WHERE name1 = name2
+		AND version1 = version2
+		AND multiarch1 = 'same'
+		AND multiarch2 = 'same';
+
+/* Packages that could be marked M-A:foreign, because they
+ *  * are Architecture: all
+ *  * do not have maintainer scripts
+ *  * do not have architecture enforcing dependencies
+ */
+DROP VIEW IF EXISTS maforeign_candidate;
+CREATE VIEW maforeign_candidate AS
+	SELECT p1.name FROM indeppackage AS p1
+	WHERE p1.multiarch = 'no'
+		AND p1.hasscripts = 0
+		AND p1.hasarchdeps = 0;
+
+/* Packages that could be converted from Arch:any to Arch:all M-A:foreign,
+ * because they
+ *  * do not have maintainer scripts
+ *  * do not ship conflicting files
+ *  * do not ship different file sets
+ *  * do not have architecture enforcing dependencies
+ */
+DROP VIEW IF EXISTS archall_candidate;
+CREATE VIEW archall_candidate AS
+	SELECT p1.name FROM archpackage AS p1
+	WHERE p1.hasscripts = 0
+		AND NOT EXISTS (
+			SELECT 1 FROM contentconflict
+			WHERE name1 = p1.name AND name2 = p1.name)
+		AND NOT EXISTS (
+			SELECT 1
+			FROM package AS p2, content AS c2, package AS p3
+			WHERE p2.name = p1.name
+				AND c2.pid = p2.id
+				AND p3.name = p1.name
+				AND NOT EXISTS (
+					SELECT 1 FROM content AS c5
+					WHERE c5.pid = p3.id
+						AND c5.filename = c2.filename))
+		AND p1.hasarchdeps = 0;
+
+/* Packages that coud be marked Multi-Arch:same, because they
+ *  * are architecture dependent
+ *  * do not have a Multi-Arch marking
+ *  * do not have maintainer scripts
+ *  * do not have file conflicts
+ */
+DROP VIEW IF EXISTS masame_candidate;
+CREATE VIEW masame_candidate AS
+	SELECT p1.name FROM archpackage AS p1
+	WHERE p1.multiarch = 'no'
+		AND p1.hasscripts = 0
+		AND NOT EXISTS (
+			SELECT 1 FROM contentconflict
+			WHERE name1 = p1.name AND name2 = p1.name);
+
+/* Package 'depender' has a dependency on 'dependee' that can be annotated with
+ * :any, because
+ *  * 'depender' is architecture independent
+ *  * 'depender' does not have maintainer scripts
+ *  * the dependency on 'dependee' is the only architecture enforcing dependency
+ *  * 'dependee' is Multi-Arch: allowed
+ */
+DROP VIEW IF EXISTS colonany_candidate;
+CREATE VIEW colonany_candidate AS
+	SELECT depender, dependee
+	FROM (
+		SELECT
+			p1.name AS depender,
+			p2.name AS dependee,
+			CASE max(p2.multiarch)
+				WHEN min(p2.multiarch) THEN p2.multiarch
+				ELSE NULL END
+				AS multiarch
+		FROM indeppackage AS p1, archdepcandidate AS c, package AS p2
+		WHERE p1.hasscripts = 0
+			AND c.dependerid = p1.id
+			AND c.dependeeid = p2.id
+		GROUP BY p1.id, p2.name)
+	GROUP BY depender
+	HAVING count(dependee) = 1
+		AND min(CASE multiarch WHEN 'allowed' THEN 1 ELSE 0 END) = 1;
+
+COMMIT;