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 @license: GNU General Public License version 2 or later
12 # This program is free software; you can redistribute it and/or modify
13 # it under the terms of the GNU General Public License as published by
14 # the Free Software Foundation; either version 2 of the License, or
15 # (at your option) any later version.
17 # This program is distributed in the hope that it will be useful,
18 # but WITHOUT ANY WARRANTY; without even the implied warranty of
19 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20 # GNU General Public License for more details.
22 # You should have received a copy of the GNU General Public License
23 # along with this program; if not, write to the Free Software
24 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
26 ################################################################################
29 from daklib.dak_exceptions import DBUpdateError
30 from daklib.config import Config
32 ################################################################################
35 Add audit schema and initial package table and triggers
41 c.execute("CREATE SCHEMA audit");
42 c.execute("GRANT USAGE on SCHEMA audit TO public")
43 c.execute("GRANT USAGE on SCHEMA audit TO ftpteam")
44 c.execute("GRANT USAGE on SCHEMA audit TO ftpmaster")
46 c.execute("""CREATE TABLE audit.package_changes (
47 changedate TIMESTAMP NOT NULL DEFAULT now(),
48 package TEXT NOT NULL,
49 version DEBVERSION NOT NULL,
50 architecture TEXT NOT NULL,
58 c.execute("GRANT INSERT ON audit.package_changes TO dak")
59 c.execute("GRANT SELECT ON audit.package_changes TO PUBLIC")
61 c.execute("""CREATE OR REPLACE FUNCTION trigger_binsrc_assoc_update() RETURNS TRIGGER AS $$
62 tablename = TD["table_name"]
65 # We only handle bin/src_associations in this trigger
66 if tablename not in ['bin_associations', 'src_associations']:
72 elif event == 'DELETE':
76 # We don't handle other changes on these tables
79 # Find suite information
80 suite_info = plpy.execute(plpy.prepare("SELECT suite_name FROM suite WHERE id = $1", ["int"]), [dat["suite"]])
82 if len(suite_info) != 1:
83 plpy.warning('Could not find suite for id %s' % dat['suite'])
85 suite_name = suite_info[0]['suite_name']
87 # Some defaults in case we can't find the overrides
92 if tablename == 'bin_associations':
93 pkg_info = plpy.execute(plpy.prepare("SELECT package, version, arch_string FROM binaries LEFT JOIN architecture ON (architecture.id = binaries.architecture) WHERE binaries.id = $1", ["int"]), [dat["bin"]])
95 # Couldn't find binary: shouldn't happen, but be careful
96 if len(pkg_info) != 1:
97 plpy.warning('Could not find binary for id %s' % dat["bin"])
100 package = pkg_info[0]['package']
101 version = pkg_info[0]['version']
102 arch = pkg_info[0]['arch_string']
104 bin_override_q = '''SELECT component.name AS component,
105 priority.priority AS priority,
106 section.section AS section,
109 LEFT JOIN override_type ON (override.type = override_type.id)
110 LEFT JOIN priority ON (priority.id = override.priority)
111 LEFT JOIN section ON (section.id = override.section)
112 LEFT JOIN component ON (override.component = component.id)
113 LEFT JOIN suite ON (suite.id = override.suite)
114 WHERE override_type.type != 'dsc'
118 bin_overrides = plpy.execute(plpy.prepare(bin_override_q, ["text", "int"]), [package, dat["suite"]])
119 # Only fill in the values if we find the unique override
120 if len(bin_overrides) == 1:
121 component = bin_overrides[0]['component']
122 priority = bin_overrides[0]['priority']
123 section = bin_overrides[0]['section']
125 elif tablename == 'src_associations':
126 pkg_info = plpy.execute(plpy.prepare("SELECT source, version FROM source WHERE source.id = $1", ["int"]), [dat["source"]])
128 # Couldn't find source: shouldn't happen, but be careful
129 if len(pkg_info) != 1:
130 plpy.warning('Could not find source for id %s' % dat["source"])
133 package = pkg_info[0]['source']
134 version = pkg_info[0]['version']
137 src_override_q = '''SELECT component.name AS component,
138 priority.priority AS priority,
139 section.section AS section,
142 LEFT JOIN override_type ON (override.type = override_type.id)
143 LEFT JOIN priority ON (priority.id = override.priority)
144 LEFT JOIN section ON (section.id = override.section)
145 LEFT JOIN component ON (override.component = component.id)
146 LEFT JOIN suite ON (suite.id = override.suite)
147 WHERE override_type.type = 'dsc'
151 src_overrides = plpy.execute(plpy.prepare(src_override_q, ["text", "int"]), [package, dat["suite"]])
152 # Only fill in the values if we find the unique override
153 if len(src_overrides) == 1:
154 component = src_overrides[0]['component']
155 priority = src_overrides[0]['priority']
156 section = src_overrides[0]['section']
158 # Insert the audit row
159 plpy.execute(plpy.prepare("INSERT INTO audit.package_changes (package, version, architecture, suite, event, priority, component, section) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)",
160 ["text", "text", "text", "text", "text", "text", "text", "text"]),
161 [package, version, arch, suite_name, pkg_event, priority, component, section])
163 $$ LANGUAGE plpythonu VOLATILE SECURITY DEFINER""")
165 c.execute("""CREATE OR REPLACE FUNCTION trigger_override_update() RETURNS TRIGGER AS $$
166 tablename = TD["table_name"]
169 if tablename != 'override':
172 if event != 'UPDATE':
173 # We only care about UPDATE event here
176 # Deal with some pathologically stupid cases we ignore
177 if (TD['new']['package'] != TD['old']['package']) or \
178 (TD['new']['type'] != TD['old']['type']) or \
179 (TD['new']['suite'] != TD['old']['suite']):
182 package = TD['old']['package']
184 # Get the priority, component and section out
185 if TD['new']['priority'] == TD['old']['priority']:
188 priority_row = plpy.execute(plpy.prepare("SELECT priority FROM priority WHERE id = $1", ["int"]), [TD['new']['priority']])
189 if len(priority_row) != 1:
190 plpy.warning('Could not find priority for id %s' % TD['new']['priority'])
192 priority = priority_row[0]['priority']
194 if TD['new']['component'] == TD['old']['component']:
197 component_row = plpy.execute(plpy.prepare("SELECT name AS component FROM component WHERE id = $1", ["int"]), [TD['new']['component']])
198 if len(component_row) != 1:
199 plpy.warning('Could not find component for id %s' % TD['new']['component'])
201 component = component_row[0]['component']
203 if TD['new']['section'] == TD['old']['section']:
206 section_row = plpy.execute(plpy.prepare("SELECT section FROM section WHERE id = $1", ["int"]), [TD['new']['section']])
207 if len(section_row) != 1:
208 plpy.warning('Could not find section for id %s' % TD['new']['section'])
210 section = section_row[0]['section']
212 # Find out if we're doing src or binary overrides
213 src_override_types = plpy.execute(plpy.prepare("SELECT id FROM override_type WHERE type = 'dsc'"), [])
214 if len(src_override_types) != 1:
216 src_override_id = src_override_types[0]['id']
218 if TD['old']['type'] == src_override_id:
219 # Doing a src_association link
220 ## Find all of the relevant suites to work on
221 for suite_row in plpy.execute(plpy.prepare('''SELECT source.version, suite_name
223 LEFT JOIN src_associations ON (source.id = src_associations.source)
224 LEFT JOIN suite ON (suite.id = src_associations.suite)
225 WHERE source.source = $1
226 AND suite = $2''', ["text", "int"]), [package, TD['new']['suite']]):
227 # INSERT one row per affected source package
228 plpy.execute(plpy.prepare("INSERT INTO audit.package_changes (package, version, architecture, suite, event, priority, component, section) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)",
229 ["text", "text", "text", "text", "text", "text", "text", "text"]),
230 [package, suite_row['version'], 'source', suite_row['suite_name'],
231 'U', priority, component, section])
233 # Doing a bin_association link; Find all of the relevant suites to work on
234 for suite_row in plpy.execute(plpy.prepare('''SELECT binaries.version, arch_string, suite_name
236 LEFT JOIN bin_associations ON (binaries.id = bin_associations.bin)
237 LEFT JOIN architecture ON (architecture.id = binaries.architecture)
238 LEFT JOIN suite ON (suite.id = bin_associations.suite)
240 AND suite = $2''', ["text", "int"]), [package, TD['new']['suite']]):
241 # INSERT one row per affected binary
242 plpy.execute(plpy.prepare("INSERT INTO audit.package_changes (package, version, architecture, suite, event, priority, component, section) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)",
243 ["text", "text", "text", "text", "text", "text", "text", "text"]),
244 [package, suite_row['version'], suite_row['arch_string'], suite_row['suite_name'],
245 'U', priority, component, section])
247 $$ LANGUAGE plpythonu VOLATILE SECURITY DEFINER;
250 c.execute("CREATE TRIGGER trigger_bin_associations_audit AFTER INSERT OR DELETE ON bin_associations FOR EACH ROW EXECUTE PROCEDURE trigger_binsrc_assoc_update()")
251 c.execute("CREATE TRIGGER trigger_src_associations_audit AFTER INSERT OR DELETE ON src_associations FOR EACH ROW EXECUTE PROCEDURE trigger_binsrc_assoc_update()")
252 c.execute("CREATE TRIGGER trigger_override_audit AFTER UPDATE ON override FOR EACH ROW EXECUTE PROCEDURE trigger_override_update()")
254 c.execute("UPDATE config SET value = '66' WHERE name = 'db_revision'")
257 except psycopg2.ProgrammingError, msg:
259 raise DBUpdateError, 'Unable to apply sick update 66, rollback issued. Error message : %s' % (str(msg))