multiarchanalyze: detect some form wrong M-A:foreign

When an arch:any package ships a .so file in a public library search
path (e.g. a symlink as many lib*-dev packages do) it most likely
shouldn't be M-A:foreign. A common exception is plugins loaded into
programs, so exclude that case.

Many thanks to Johannes Schauer and Guillem Jover for helping discover
this pattern of Multi-Arch: foreign abuse.

1 files changed, 20 insertions, 0 deletions

diff --git a/multiarchanalyze.sql b/multiarchanalyze.sql
index 95ebd7d..b98092f 100644
--- a/multiarchanalyze.sql
+++ b/multiarchanalyze.sql
@@ -196,4 +196,24 @@ CREATE VIEW colonany_candidate AS
 	HAVING count(dependee) = 1
 		AND min(CASE multiarch WHEN 'allowed' THEN 1 ELSE 0 END) = 1;
 
+/* Packages that are wrongly marked Multi-Arch:foreign, because they
+ *  * are architecture dependent
+ *  * ship a shared library in a public path
+ *  * and the library is not a plugin for a contained program
+ */
+DROP VIEW IF EXISTS maforeign_library;
+CREATE VIEW maforeign_library AS
+	SELECT DISTINCT p.name
+	FROM package AS p
+	WHERE architecture != 'all'
+		AND multiarch = 'foreign'
+		AND EXISTS (
+			SELECT 1 FROM content
+			WHERE pid = p.id
+				AND filename REGEXP '^\./(usr/)?lib/([a-z0-9_]*-linux-gnu[a-z0-9_*]*/)?lib.*\.so$')
+		AND NOT EXISTS (
+			SELECT 1 FROM content
+			WHERE pid = p.id
+				AND filename REGEXP '^\./(usr/)?s?bin/.*');
+
 COMMIT;