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
|
CREATE TABLE packages (
id int UNIQUE NOT NULL,
name text NOT NULL,
version text NOT NULL,
format text NOT NULL,
loc text NOT NULL,
diffgz_name text DEFAULT NULL,
diffgz_size int DEFAULT NULL,
diffgz_md5sum varchar(32) DEFAULT NULL
);
CREATE TABLE suites (
id int UNIQUE NOT NULL,
name text UNIQUE NOT NULL PRIMARY KEY
);
CREATE TABLE components (
id int UNIQUE NOT NULL,
name text PRIMARY KEY
);
CREATE TABLE package_rel_map (
package_id int NOT NULL
CONSTRAINT fk_package_id REFERENCES packages(id),
suite_id int NOT NULL
CONSTRAINT fk_suite_id REFERENCES suites(id) ON DELETE CASCADE,
component_id int NOT NULL
CONSTRAINT fk_component_id REFERENCES components(id)
);
-- 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;
CREATE TRIGGER fku_package_rel_map_prune_packages_package_id
BEFORE UPDATE ON [package_rel_map]
FOR EACH ROW BEGIN
DELETE FROM packages WHERE packages.id = OLD.package_id
AND (OLD.package_id != NEW.package_id)
AND (SELECT package_id FROM package_rel_map
WHERE package_id = OLD.package_id AND rowid != OLD.rowid)
IS NULL;
END;
INSERT INTO packages VALUES (0,'','','','',NULL,NULL,NULL);
INSERT INTO suites VALUES (0,'');
INSERT INTO components VALUES (0,'');
|
