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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
|
-- db schema for patch tracker. note that every distinct command (omitting
-- comments) must be preceded and followed by a blank line.
CREATE TABLE packages (
id INTEGER UNIQUE NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
version TEXT NOT NULL,
format TEXT NOT NULL,
loc TEXT NOT NULL,
diffgz_name TEXT DEFAULT NULL,
diffgz_size INTEGER DEFAULT NULL,
diffgz_md5sum TEXT DEFAULT NULL
);
CREATE UNIQUE INDEX idx_pkg_name_version ON packages (name,version);
CREATE TABLE suites (
id INTEGER UNIQUE NOT NULL PRIMARY KEY,
name TEXT UNIQUE NOT NULL,
UNIQUE (name) ON CONFLICT IGNORE
);
CREATE TABLE components (
id INTEGER UNIQUE NOT NULL PRIMARY KEY,
name TEXT UNIQUE NOT NULL,
UNIQUE (name) ON CONFLICT IGNORE
);
CREATE TABLE package_rel_map (
package_id INTEGER NOT NULL
CONSTRAINT fk_package_id REFERENCES packages(id),
suite_id INTEGER NOT NULL
CONSTRAINT fk_suite_id REFERENCES suites(id) ON DELETE CASCADE,
component_id INTEGER NOT NULL
CONSTRAINT fk_component_id REFERENCES components(id),
marked INTEGER NOT NULL DEFAULT 1,
UNIQUE (package_id, suite_id, component_id) ON CONFLICT REPLACE
);
-- sqlite doesn't actually implement FK constraints, therefore we
-- help out a little and do it ourselves via triggers
-- before insert on the package relation mapping, catch invalid references
CREATE TRIGGER fki_package_rel_map_suite_id_suites_id
BEFORE INSERT ON [package_rel_map]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'INSERT on "package_rel_map" violates FK constraint on "suites.id"')
WHERE (SELECT id FROM suites WHERE id = NEW.suite_id) IS NULL;
END;
CREATE TRIGGER fki_package_rel_map_package_id_packages_id
BEFORE INSERT ON [package_rel_map]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'INSERT on "package_rel_map" violates FK constraint on "packages.id"')
WHERE (SELECT id FROM packages WHERE id = NEW.package_id) IS NULL;
END;
CREATE TRIGGER fki_package_rel_map_component_id_components_id
BEFORE INSERT ON [package_rel_map]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'INSERT on "package_rel_map" violates FK constraint on "components.id"')
WHERE (SELECT id FROM components WHERE id =NEW.component_id) IS NULL;
END;
-- the same but for updates
CREATE TRIGGER fku_package_rel_map_suite_id_suites_id
BEFORE UPDATE ON [package_rel_map]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'update on "package_rel_map" violates FK constraint on "suites.id"')
WHERE (SELECT id FROM suites WHERE id = NEW.suite_id) IS NULL;
END;
CREATE TRIGGER fku_package_rel_map_package_id_packages_id
BEFORE UPDATE ON [package_rel_map]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'update on "package_rel_map" violates FK constraint on "packages.id"')
WHERE (SELECT id FROM packages WHERE id = NEW.package_id) IS NULL;
END;
CREATE TRIGGER fku_package_rel_map_component_id_components_id
BEFORE UPDATE ON [package_rel_map]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'update on "package_rel_map" violates FK constraint on "components.id"')
WHERE (SELECT id FROM components WHERE id=NEW.component_id) IS NULL;
END;
-- when a package/component/suite id is deleted, clean up any references
-- that would otherwise be orphaned
CREATE TRIGGER fkdc_package_rel_map_suite_id_suites_id
BEFORE DELETE ON suites
FOR EACH ROW BEGIN
DELETE FROM package_rel_map WHERE package_rel_map.suite_id = OLD.id;
END;
CREATE TRIGGER fkdc_package_rel_map_component_id_components_id
BEFORE DELETE ON components
FOR EACH ROW BEGIN
DELETE FROM package_rel_map WHERE package_rel_map.component_id = OLD.id;
END;
CREATE TRIGGER fkdc_package_rel_map_package_id_packages_id
BEFORE DELETE ON packages
FOR EACH ROW BEGIN
DELETE FROM package_rel_map WHERE package_rel_map.package_id = OLD.id;
END;
-- when a package relation mapping is deleted or updated, delete the package
-- record for the old relation if no other references to that package exist
CREATE TRIGGER fkdc_packages_id_package_rel_map_package_id
BEFORE DELETE ON package_rel_map
FOR EACH ROW BEGIN
DELETE FROM packages WHERE packages.id = OLD.package_id
AND (SELECT package_id FROM package_rel_map
WHERE package_id = OLD.package_id AND rowid != OLD.rowid)
IS NULL;
END;
|
