diff options
Diffstat (limited to 'multiarchanalyze.sql')
| -rw-r--r-- | multiarchanalyze.sql | 199 |
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; |