/
wolpertinger.sql
75 lines (62 loc) · 1.54 KB
/
wolpertinger.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
CREATE TABLE default_ports (
port_string varchar(32)
);
CREATE TABLE drone (
id integer primary key autoincrement,
ip integer unique
);
CREATE TABLE drone_usage (
drone_id integer references drone(id),
port integer,
type integer,
scan_id references scan(id),
primary key(drone_id, type, scan_id)
);
CREATE TABLE host_table (
id integer primary key autoincrement,
ip integer unique,
hostname varchar(255),
ref_count integer default 1
);
CREATE TABLE result (
port integer,
host_id references host(id),
scan_id references scan(id),
primary key(port, host_id, scan_id)
);
CREATE TABLE scan (
id integer primary key autoincrement,
tag varchar(64),
hosts integer,
ports integer,
pps integer,
source_ip integer,
source_port integer,
start_time date,
end_time date
);
CREATE TABLE drone_credentials (
id integer primary key autoincrement,
uuid varchar(64) unique,
username varchar(128),
password varchar(128)
);
CREATE TABLE services (
name varchar(64),
port integer primary key,
description varchar(128)
);
CREATE VIEW host as select * from host_table;
CREATE TRIGGER delete_name_if_zero AFTER UPDATE ON host_table
BEGIN
delete from host_table where ref_count <= 0;
END;
CREATE TRIGGER safe_delete INSTEAD OF DELETE ON host
BEGIN
UPDATE host_table SET ref_count = ref_count - 1 where ip = old.ip;
END;
CREATE TRIGGER safe_insert INSTEAD OF INSERT ON host
BEGIN
UPDATE host_table SET ref_count = ref_count + 1 where ip = new.ip;
INSERT OR FAIL INTO host_table (ip,hostname) VALUES(new.ip, new.hostname);
END;