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
121
122
123
124
125
126
127
128
129
130
131
132
133
134
|
-- 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 maintainers (
id INTEGER UNIQUE NOT NULL PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
UNIQUE (email) ON CONFLICT IGNORE
);
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
);
CREATE TABLE maint_rel_map (
package_id INTEGER NOT NULL
CONSTRAINT fk_package_id REFERENCES packages(id),
maintainer_id INTEGER NOT NULL
CONSTRAINT fk_maintainer_id REFERENCES maintainers(id) ON DELETE CASCADE,
UNIQUE (package_id, maintainer_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
BEFORE INSERT ON [package_rel_map]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'INSERT FK violation: package_rel_map/suites.id')
WHERE (SELECT id FROM suites WHERE id = NEW.suite_id) IS NULL;
SELECT RAISE(ROLLBACK, 'INSERT FK violation: package_rel_map/packages.id')
WHERE (SELECT id FROM packages WHERE id = NEW.package_id) IS NULL;
SELECT RAISE(ROLLBACK, 'INSERT FK violation: package_rel_map/components.id')
WHERE (SELECT id FROM components WHERE id =NEW.component_id) IS NULL;
END;
-- likewise for the package maintainer mapping
CREATE TRIGGER fki_maint_rel_map
BEFORE INSERT ON [maint_rel_map]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'INSERT FK violation: maint_rel_map/packages.id')
WHERE (SELECT id FROM packages WHERE id = NEW.package_id) IS NULL;
SELECT RAISE(ROLLBACK, 'INSERT FK violation: maint_rel_map/maintainers.id')
WHERE (SELECT id FROM maintainers WHERE id = NEW.maintainer_id) IS NULL;
END;
-- the same but for updates
CREATE TRIGGER fku_package_rel_map
BEFORE UPDATE ON [package_rel_map]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'UPDATE FK violation: package_rel_map/packages.id')
WHERE (SELECT id FROM packages WHERE id = NEW.package_id) IS NULL;
SELECT RAISE(ROLLBACK, 'UPDATE FK violation: package_rel_map/components.id')
WHERE (SELECT id FROM components WHERE id=NEW.component_id) IS NULL;
SELECT RAISE(ROLLBACK, 'UPDATE FK violation: package_rel_map/suites.id')
WHERE (SELECT id FROM suites WHERE id = NEW.suite_id) IS NULL;
END;
CREATE TRIGGER fku_maint_rel_map
BEFORE UPDATE ON [maint_rel_map]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'UPDATE FK violation: maint_rel_map/packages.id')
WHERE (SELECT id FROM packages WHERE id = NEW.package_id) IS NULL;
SELECT RAISE(ROLLBACK, 'UPDATE FK violation: maint_rel_map/maintainers.id')
WHERE (SELECT id FROM maintainers WHERE id=NEW.maintainer_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;
DELETE FROM maint_rel_map WHERE maint_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;
|
