5 Add audit schema and initial package table and triggers
7 @contact: Debian FTP Master <ftpmaster@debian.org>
8 @copyright: 2011 Mark Hymers <mhy@debian.org>
9 @copyright: 2011 Ansgar Burchardt <ansgar@debian.org>
10 @license: GNU General Public License version 2 or later
13 # This program is free software; you can redistribute it and/or modify
14 # it under the terms of the GNU General Public License as published by
15 # the Free Software Foundation; either version 2 of the License, or
16 # (at your option) any later version.
18 # This program is distributed in the hope that it will be useful,
19 # but WITHOUT ANY WARRANTY; without even the implied warranty of
20 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
21 # GNU General Public License for more details.
23 # You should have received a copy of the GNU General Public License
24 # along with this program; if not, write to the Free Software
25 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
27 ################################################################################
30 from daklib.dak_exceptions import DBUpdateError
31 from daklib.config import Config
33 ################################################################################
36 Add audit schema and initial package table and triggers
42 c.execute("CREATE SCHEMA audit");
43 c.execute("GRANT USAGE on SCHEMA audit TO public")
44 c.execute("GRANT USAGE on SCHEMA audit TO ftpteam")
45 c.execute("GRANT USAGE on SCHEMA audit TO ftpmaster")
47 c.execute("""CREATE TABLE audit.package_changes (
48 changedate TIMESTAMP NOT NULL DEFAULT now(),
49 package TEXT NOT NULL,
50 version DEBVERSION NOT NULL,
51 architecture TEXT NOT NULL,
59 c.execute("GRANT INSERT ON audit.package_changes TO dak")
60 c.execute("GRANT SELECT ON audit.package_changes TO PUBLIC")
62 c.execute("""CREATE OR REPLACE FUNCTION trigger_binsrc_assoc_update() RETURNS TRIGGER AS $$
66 v_package audit.package_changes.package%TYPE;
67 v_version audit.package_changes.version%TYPE;
68 v_architecture audit.package_changes.architecture%TYPE;
69 v_suite audit.package_changes.suite%TYPE;
70 v_event audit.package_changes.event%TYPE;
71 v_priority audit.package_changes.priority%TYPE;
72 v_component audit.package_changes.component%TYPE;
73 v_section audit.package_changes.section%TYPE;
76 WHEN 'INSERT' THEN v_event := 'I'; v_data := NEW;
77 WHEN 'DELETE' THEN v_event := 'D'; v_data := OLD;
78 ELSE RAISE EXCEPTION 'trigger called for invalid operation (%)', TG_OP;
81 SELECT suite_name INTO STRICT v_suite FROM suite WHERE id = v_data.suite;
84 WHEN 'bin_associations' THEN
85 SELECT package, version, arch_string
86 INTO STRICT v_package, v_version, v_architecture
87 FROM binaries LEFT JOIN architecture ON (architecture.id = binaries.architecture)
88 WHERE binaries.id = v_data.bin;
90 SELECT component.name, priority.priority, section.section
91 INTO v_component, v_priority, v_section
93 JOIN override_type ON (override.type = override_type.id)
94 JOIN priority ON (priority.id = override.priority)
95 JOIN section ON (section.id = override.section)
96 JOIN component ON (override.component = component.id)
97 JOIN suite ON (suite.id = override.suite)
98 WHERE override_type.type != 'dsc'
99 AND override.package = v_package AND suite.id = v_data.suite;
101 WHEN 'src_associations' THEN
102 SELECT source, version
103 INTO STRICT v_package, v_version
104 FROM source WHERE source.id = v_data.source;
105 v_architecture := 'source';
107 SELECT component.name, priority.priority, section.section
108 INTO v_component, v_priority, v_section
110 JOIN override_type ON (override.type = override_type.id)
111 JOIN priority ON (priority.id = override.priority)
112 JOIN section ON (section.id = override.section)
113 JOIN component ON (override.component = component.id)
114 JOIN suite ON (suite.id = override.suite)
115 WHERE override_type.type = 'dsc'
116 AND override.package = v_package AND suite.id = v_data.suite;
118 ELSE RAISE EXCEPTION 'trigger called for invalid table (%)', TG_TABLE_NAME;
121 INSERT INTO audit.package_changes
122 (package, version, architecture, suite, event, priority, component, section)
123 VALUES (v_package, v_version, v_architecture, v_suite, v_event, v_priority, v_component, v_section);
127 $$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER
128 SET search_path = public, pg_temp""");
130 c.execute("""CREATE OR REPLACE FUNCTION trigger_override_update() RETURNS TRIGGER AS $$
132 v_src_override_id override_type.id%TYPE;
134 v_priority audit.package_changes.priority%TYPE := NULL;
135 v_component audit.package_changes.component%TYPE := NULL;
136 v_section audit.package_changes.section%TYPE := NULL;
139 IF TG_TABLE_NAME != 'override' THEN
140 RAISE EXCEPTION 'trigger called for invalid table (%)', TG_TABLE_NAME;
142 IF TG_OP != 'UPDATE' THEN
143 RAISE EXCEPTION 'trigger called for invalid event (%)', TG_OP;
146 IF OLD.package != NEW.package OR OLD.type != NEW.type OR OLD.suite != NEW.suite THEN
150 IF OLD.priority != NEW.priority THEN
151 SELECT priority INTO STRICT v_priority FROM priority WHERE id = NEW.priority;
154 IF OLD.component != NEW.component THEN
155 SELECT name INTO STRICT v_component FROM component WHERE id = NEW.component;
158 IF OLD.section != NEW.section THEN
159 SELECT section INTO STRICT v_section FROM section WHERE id = NEW.section;
162 -- Find out if we're doing src or binary overrides
163 SELECT id INTO STRICT v_src_override_id FROM override_type WHERE type = 'dsc';
164 IF OLD.type = v_src_override_id THEN
165 -- Doing a src_association link
166 INSERT INTO audit.package_changes
167 (package, version, architecture, suite, event, priority, component, section)
168 SELECT NEW.package, source.version, 'source', suite.suite_name, 'U', v_priority, v_component, v_section
170 JOIN src_associations ON (source.id = src_associations.source)
171 JOIN suite ON (suite.id = src_associations.suite)
172 WHERE source.source = NEW.package AND src_associations.suite = NEW.suite;
174 -- Doing a bin_association link
175 INSERT INTO audit.package_changes
176 (package, version, architecture, suite, event, priority, component, section)
177 SELECT NEW.package, binaries.version, architecture.arch_string, suite.suite_name, 'U', v_priority, v_component, v_section
179 JOIN bin_associations ON (binaries.id = bin_associations.bin)
180 JOIN architecture ON (architecture.id = binaries.architecture)
181 JOIN suite ON (suite.id = bin_associations.suite)
182 WHERE binaries.package = NEW.package AND bin_associations.suite = NEW.suite;
187 $$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER
188 SET search_path = public, pg_temp""");
190 c.execute("CREATE TRIGGER trigger_bin_associations_audit AFTER INSERT OR DELETE ON bin_associations FOR EACH ROW EXECUTE PROCEDURE trigger_binsrc_assoc_update()")
191 c.execute("CREATE TRIGGER trigger_src_associations_audit AFTER INSERT OR DELETE ON src_associations FOR EACH ROW EXECUTE PROCEDURE trigger_binsrc_assoc_update()")
192 c.execute("CREATE TRIGGER trigger_override_audit AFTER UPDATE ON override FOR EACH ROW EXECUTE PROCEDURE trigger_override_update()")
194 c.execute("UPDATE config SET value = '67' WHERE name = 'db_revision'")
197 except psycopg2.ProgrammingError, msg:
199 raise DBUpdateError('Unable to apply sick update 67, rollback issued. Error message : %s' % (str(msg)))