+ c.execute("CREATE SCHEMA audit");
+ c.execute("GRANT USAGE on SCHEMA audit TO public")
+ c.execute("GRANT USAGE on SCHEMA audit TO ftpteam")
+ c.execute("GRANT USAGE on SCHEMA audit TO ftpmaster")
+
+ c.execute("""CREATE TABLE audit.package_changes (
+ changedate TIMESTAMP NOT NULL DEFAULT now(),
+ package TEXT NOT NULL,
+ version DEBVERSION NOT NULL,
+ architecture TEXT NOT NULL,
+ suite TEXT NOT NULL,
+ event TEXT NOT NULL,
+ priority TEXT,
+ component TEXT,
+ section TEXT
+)""")
+
+ c.execute("GRANT INSERT ON audit.package_changes TO dak")
+ c.execute("GRANT SELECT ON audit.package_changes TO PUBLIC")
+
+ c.execute("""CREATE OR REPLACE FUNCTION trigger_binsrc_assoc_update() RETURNS TRIGGER AS $$
+DECLARE
+ v_data RECORD;
+
+ v_package audit.package_changes.package%TYPE;
+ v_version audit.package_changes.version%TYPE;
+ v_architecture audit.package_changes.architecture%TYPE;
+ v_suite audit.package_changes.suite%TYPE;
+ v_event audit.package_changes.event%TYPE;
+ v_priority audit.package_changes.priority%TYPE;
+ v_component audit.package_changes.component%TYPE;
+ v_section audit.package_changes.section%TYPE;
+BEGIN
+ CASE TG_OP
+ WHEN 'INSERT' THEN v_event := 'I'; v_data := NEW;
+ WHEN 'DELETE' THEN v_event := 'D'; v_data := OLD;
+ ELSE RAISE EXCEPTION 'trigger called for invalid operation (%)', TG_OP;
+ END CASE;
+
+ SELECT suite_name INTO STRICT v_suite FROM suite WHERE id = v_data.suite;
+
+ CASE TG_TABLE_NAME
+ WHEN 'bin_associations' THEN
+ SELECT package, version, arch_string
+ INTO STRICT v_package, v_version, v_architecture
+ FROM binaries LEFT JOIN architecture ON (architecture.id = binaries.architecture)
+ WHERE binaries.id = v_data.bin;
+
+ SELECT component.name, priority.priority, section.section
+ INTO v_component, v_priority, v_section
+ FROM override
+ JOIN override_type ON (override.type = override_type.id)
+ JOIN priority ON (priority.id = override.priority)
+ JOIN section ON (section.id = override.section)
+ JOIN component ON (override.component = component.id)
+ JOIN suite ON (suite.id = override.suite)
+ WHERE override_type.type != 'dsc'
+ AND override.package = v_package AND suite.id = v_data.suite;
+
+ WHEN 'src_associations' THEN
+ SELECT source, version
+ INTO STRICT v_package, v_version
+ FROM source WHERE source.id = v_data.source;
+ v_architecture := 'source';
+
+ SELECT component.name, priority.priority, section.section
+ INTO v_component, v_priority, v_section
+ FROM override
+ JOIN override_type ON (override.type = override_type.id)
+ JOIN priority ON (priority.id = override.priority)
+ JOIN section ON (section.id = override.section)
+ JOIN component ON (override.component = component.id)
+ JOIN suite ON (suite.id = override.suite)
+ WHERE override_type.type = 'dsc'
+ AND override.package = v_package AND suite.id = v_data.suite;
+
+ ELSE RAISE EXCEPTION 'trigger called for invalid table (%)', TG_TABLE_NAME;
+ END CASE;
+
+ INSERT INTO audit.package_changes
+ (package, version, architecture, suite, event, priority, component, section)
+ VALUES (v_package, v_version, v_architecture, v_suite, v_event, v_priority, v_component, v_section);
+
+ RETURN NEW;
+END;
+$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER
+SET search_path = public, pg_temp""");
+
+ c.execute("""CREATE OR REPLACE FUNCTION trigger_override_update() RETURNS TRIGGER AS $$