display "issues" with files in package view

Currently this is invalid .gz files and png files not named .png.

3 files changed, 17 insertions, 0 deletions

diff --git a/schema.sql b/schema.sql
index e9e0bcc..8a94882 100644
--- a/schema.sql
+++ b/schema.sql
@@ -9,3 +9,4 @@ CREATE INDEX hash_hash_index ON hash (hash);
 CREATE TABLE sharing (pid1 INTEGER, pid2 INTEGER, func1 TEXT, func2 TEXT, files INTEGER, size INTEGER, FOREIGN KEY (pid1) REFERENCES package(id) ON DELETE CASCADE, FOREIGN KEY (pid2) REFERENCES package(id) ON DELETE CASCADE);
 CREATE INDEX sharing_insert_index ON sharing (pid1, pid2, func1, func2);
 CREATE TABLE duplicate (cid INTEGER PRIMARY KEY, FOREIGN KEY (cid) REFERENCES content(id) ON DELETE CASCADE);
+CREATE TABLE issue (cid INTEGER REFERENCES content(id) ON DELETE CASCADE, issue TEXT);
diff --git a/update_sharing.py b/update_sharing.py
index 55e8096..62a3ab5 100755
--- a/update_sharing.py
+++ b/update_sharing.py
@@ -43,6 +43,7 @@ def main():
     cur.execute("PRAGMA foreign_keys = ON;")
     cur.execute("DELETE FROM sharing;")
     cur.execute("DELETE FROM duplicate;")
+    cur.execute("DELETE FROM issue;")
     readcur = db.cursor()
     readcur.execute("SELECT hash FROM hash GROUP BY hash HAVING count(*) > 1;")
     for hashvalue, in fetchiter(readcur):
@@ -54,6 +55,8 @@ def main():
         cur.executemany("INSERT OR IGNORE INTO duplicate (cid) VALUES (?);",
                         [(row[1],) for row in rows])
         process_pkgdict(cur, pkgdict)
+    cur.execute("INSERT INTO issue (cid, issue) SELECT content.id, 'file named something.gz is not a valid gzip file' FROM content WHERE content.filename LIKE '%.gz' AND NOT EXISTS (SELECT 1 FROM hash WHERE hash.cid = content.id AND hash.function = 'gzip_sha512');")
+    cur.execute("INSERT INTO issue (cid, issue) SELECT content.id, 'png image not named something.png' FROM content JOIN hash ON content.id = hash.cid WHERE function = 'image_sha512' AND lower(filename) NOT LIKE '%.png';")
     db.commit()
 
 if __name__ == "__main__":
diff --git a/webapp.py b/webapp.py
index b5e0c63..c442ebe 100755
--- a/webapp.py
+++ b/webapp.py
@@ -69,6 +69,14 @@ package_template = jinjaenv.from_string(
     {%- endfor -%}
 <p>Note: Packages with yellow background are required to be installed when this package is installed.</p>
 {%- endif -%}
+{%- if issues -%}
+    <h3>issues with particular files</h3>
+    <table border='1'><tr><th>filename</th><th>issue</th></tr>
+    {%- for filename, issue in issues|dictsort(true) -%}
+        <tr><td><span class="filename">{{ filename|e }}</span></td><td>{{ issue|e }}</td></tr>
+    {%- endfor -%}
+    </table>
+{%- endif -%}
 {% endblock %}""")
 
 detail_template = jinjaenv.from_string(
@@ -271,6 +279,11 @@ class Application(object):
         params["dependencies"] = self.get_dependencies(params["pid"])
         params["shared"] = self.cached_sharedstats(params["pid"])
         params["urlroot"] = ".."
+        cur = self.db.cursor()
+        cur.execute("SELECT content.filename, issue.issue FROM content JOIN issue ON content.id = issue.cid WHERE content.pid = ?;",
+                    (params["pid"],))
+        params["issues"] = dict(cur.fetchall())
+        cur.close()
         return html_response(package_template.render(params))
 
     def compute_comparison(self, pid1, pid2):