| 16 | |
| 17 | |
| 18 | def initialize_schema(connection): |
| 19 | cur = connection.cursor() |
| 20 | |
| 21 | cur.execute(""" |
| 22 | CREATE TABLE scans ( |
| 23 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 24 | package_name VARCHAR(256) UNIQUE, |
| 25 | metadata JSON |
| 26 | ) |
| 27 | """) |
| 28 | |
| 29 | cur.execute(""" |
| 30 | CREATE TABLE tag_names (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(128) UNIQUE) |
| 31 | """) |
| 32 | |
| 33 | cur.execute(""" |
| 34 | CREATE TABLE detection_types (id INTEGER PRIMARY KEY AUTOINCREMENT, name text) |
| 35 | """) |
| 36 | |
| 37 | cur.execute(""" |
| 38 | CREATE TABLE detections |
| 39 | ( |
| 40 | id INTEGER PRIMARY KEY AUTOINCREMENT , |
| 41 | scan INTEGER NOT NULL , |
| 42 | type INTEGER NOT NULL, |
| 43 | signature TEXT UNIQUE, |
| 44 | message TEXT NOT NULL, |
| 45 | score INTEGER DEFAULT 0, |
| 46 | extra BLOB, |
| 47 | FOREIGN KEY (type) REFERENCES detection_types (id), |
| 48 | FOREIGN KEY (scan) REFERENCES scans (id) |
| 49 | ) |
| 50 | """) |
| 51 | |
| 52 | cur.execute(""" |
| 53 | CREATE TABLE tags ( |
| 54 | detection INTEGER NOT NULL, |
| 55 | tag INTEGER NOT NULL, |
| 56 | FOREIGN KEY (detection) REFERENCES detections (id), |
| 57 | FOREIGN KEY (tag) REFERENCES tag_names (id), |
| 58 | PRIMARY KEY (detection, tag) |
| 59 | ) WITHOUT ROWID |
| 60 | """) |
| 61 | |
| 62 | connection.commit() |
| 63 | |
| 64 | |
| 65 | def create_or_open(name: str): |