X-Git-Url: https://git.decadent.org.uk/gitweb/?a=blobdiff_plain;f=dak%2Fdakdb%2Fupdate66.py;h=d59306fa6536001990df09b3bb4fdee072ea21af;hb=f308b558967796bd5a43013346d8b204592d9b93;hp=10c1583b8f4b005010e0cd7982f2ea586345ea6c;hpb=0fb5e8ddc348ec4f4cccd9663ddeeba191d1a149;p=dak.git diff --git a/dak/dakdb/update66.py b/dak/dakdb/update66.py old mode 100755 new mode 100644 index 10c1583b..d59306fa --- a/dak/dakdb/update66.py +++ b/dak/dakdb/update66.py @@ -2,10 +2,10 @@ # coding=utf8 """ -Add audit schema and initial package table and triggers +Add 2 partial indexes to speed up dak rm. @contact: Debian FTP Master -@copyright: 2011 Mark Hymers +@copyright: 2011 Torsten Werner @license: GNU General Public License version 2 or later """ @@ -32,213 +32,29 @@ from daklib.config import Config ################################################################################ def do_update(self): """ - Add audit schema and initial package table and triggers + Add 2 partial indexes to speed up dak rm. """ print __doc__ try: - c = self.db.cursor() - - 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 $$ -tablename = TD["table_name"] -event = TD["event"] - -# We only handle bin/src_associations in this trigger -if tablename not in ['bin_associations', 'src_associations']: - return None - -if event == 'INSERT': - dat = TD['new'] - pkg_event = 'I' -elif event == 'DELETE': - dat = TD['old'] - pkg_event = 'D' -else: - # We don't handle other changes on these tables - return None - -# Find suite information -suite_info = plpy.execute(plpy.prepare("SELECT suite_name FROM suite WHERE id = $1", ["int"]), [dat["suite"]]) -# Couldn't find suite -if len(suite_info) != 1: - return None -suite_name = suite_info[0]['suite_name'] - -# Some defaults in case we can't find the overrides -component = "" -section = "" -priority = "" - -if tablename == 'bin_associations': - 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"]]) - - # Couldn't find binary: shouldn't happen, but be careful - if len(pkg_info) != 1: - return None - - package = pkg_info[0]['package'] - version = pkg_info[0]['version'] - arch = pkg_info[0]['arch_string'] - - bin_override_q = '''SELECT component.name AS component, - priority.priority AS priority, - section.section AS section, - override_type.type - FROM override - LEFT JOIN override_type ON (override.type = override_type.id) - LEFT JOIN priority ON (priority.id = override.priority) - LEFT JOIN section ON (section.id = override.section) - LEFT JOIN component ON (override.component = component.id) - LEFT JOIN suite ON (suite.id = override.suite) - WHERE override_type.type != 'dsc' - AND package = $1 - AND suite.id = $2''' - - bin_overrides = plpy.execute(plpy.prepare(bin_override_q, ["text", "int"]), [package, dat["suite"]]) - # Only fill in the values if we find the unique override - if len(bin_overrides) == 1: - component = bin_overrides[0]['component'] - priority = bin_overrides[0]['priority'] - section = bin_overrides[0]['section'] - -elif tablename == 'src_associations': - pkg_info = plpy.execute(plpy.prepare("SELECT source, version FROM source WHERE source.id = $1", ["int"]), [dat["source"]]) - - # Couldn't find source: shouldn't happen, but be careful - if len(pkg_info) != 1: - return None + cnf = Config() - package = pkg_info[0]['source'] - version = pkg_info[0]['version'] - arch = 'source' - - src_override_q = '''SELECT component.name AS component, - priority.priority AS priority, - section.section AS section, - override_type.type - FROM override - LEFT JOIN override_type ON (override.type = override_type.id) - LEFT JOIN priority ON (priority.id = override.priority) - LEFT JOIN section ON (section.id = override.section) - LEFT JOIN component ON (override.component = component.id) - LEFT JOIN suite ON (suite.id = override.suite) - WHERE override_type.type = 'dsc' - AND package = $1 - AND suite.id = $2''' - - src_overrides = plpy.execute(plpy.prepare(src_override_q, ["text", "int"]), [package, dat["suite"]]) - # Only fill in the values if we find the unique override - if len(src_overrides) == 1: - component = src_overrides[0]['component'] - priority = src_overrides[0]['priority'] - section = src_overrides[0]['section'] - -# Insert the audit row -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)", - ["text", "text", "text", "text", "text", "text", "text", "text"]), - [package, version, arch, suite_name, pkg_event, priority, component, section]) - -$$ LANGUAGE plpythonu VOLATILE SECURITY DEFINER""") - - c.execute("""CREATE OR REPLACE FUNCTION trigger_override_update() RETURNS TRIGGER AS $$ -tablename = TD["table_name"] -event = TD["event"] - -if tablename != 'override': - return None - -if event != 'UPDATE': - # We only care about UPDATE event here - return None - -# Deal with some pathologically stupid cases we ignore -if (TD['new']['package'] != TD['old']['package']) or \ - (TD['new']['type'] != TD['old']['type']) or \ - (TD['new']['suite'] != TD['old']['suite']): - return None - -package = TD['old']['package'] - -# Get the priority, component and section out -priority_row = plpy.execute(plpy.prepare("SELECT priority FROM priority WHERE id = $1", ["int"]), [TD['new']['priority']]) -if len(priority_row) != 1: - return None -priority = priority_row[0]['priority'] - -component_row = plpy.execute(plpy.prepare("SELECT name AS component FROM component WHERE id = $1", ["int"]), [TD['new']['component']]) -if len(component_row) != 1: - return None -component = component_row[0]['component'] - -section_row = plpy.execute(plpy.prepare("SELECT section FROM section WHERE id = $1", ["int"]), [TD['new']['section']]) -if len(section_row) != 1: - return None -section = section_row[0]['section'] - -# Find out if we're doing src or binary overrides -src_override_types = plpy.execute(plpy.prepare("SELECT id FROM override_type WHERE type = 'dsc'"), []) -if len(src_override_types) != 1: - return None -src_override_id = src_override_types[0]['id'] - -if TD['old']['type'] == src_override_id: - # Doing a src_association link - ## Find all of the relevant suites to work on - for suite_row in plpy.execute(plpy.prepare('''SELECT source.version, suite_name - FROM source - LEFT JOIN src_associations ON (source.id = src_associations.source) - LEFT JOIN suite ON (suite.id = src_associations.suite) - WHERE source.source = $1 - AND suite = $2''', ["text", "int"]), [package, TD['new']['suite']]): - # INSERT one row per affected source package - 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)", - ["text", "text", "text", "text", "text", "text", "text", "text"]), - [package, suite_row['version'], 'source', suite_row['suite_name'], - 'U', priority, component, section]) -else: - # Doing a bin_association link; Find all of the relevant suites to work on - for suite_row in plpy.execute(plpy.prepare('''SELECT binaries.version, arch_string, suite_name - FROM binaries - LEFT JOIN bin_associations ON (binaries.id = bin_associations.bin) - LEFT JOIN architecture ON (architecture.id = binaries.architecture) - LEFT JOIN suite ON (suite.id = bin_associations.suite) - WHERE package = $1 - AND suite = $2''', ["text", "int"]), [package, TD['new']['suite']]): - # INSERT one row per affected binary - 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)", - ["text", "text", "text", "text", "text", "text", "text", "text"]), - [package, suite_row['version'], suite_row['arch_string'], suite_row['suite_name'], - 'U', priority, component, section]) + c = self.db.cursor() -$$ LANGUAGE plpythonu VOLATILE SECURITY DEFINER; -""") + # partial index for Depends + c.execute("SELECT key_id FROM metadata_keys WHERE key = 'Depends'") + key = c.fetchone()[0] + c.execute("""CREATE INDEX binaries_metadata_depends + ON binaries_metadata (bin_id) WHERE key_id = %d""" % key) - c.execute("CREATE TRIGGER trigger_bin_associations_audit AFTER INSERT OR DELETE ON bin_associations FOR EACH ROW EXECUTE PROCEDURE trigger_binsrc_assoc_update()") - c.execute("CREATE TRIGGER trigger_src_associations_audit AFTER INSERT OR DELETE ON src_associations FOR EACH ROW EXECUTE PROCEDURE trigger_binsrc_assoc_update()") - c.execute("CREATE TRIGGER trigger_override_audit AFTER UPDATE ON override FOR EACH ROW EXECUTE PROCEDURE trigger_override_update()") + # partial index for Provides + c.execute("SELECT key_id FROM metadata_keys WHERE key = 'Provides'") + key = c.fetchone()[0] + c.execute("""CREATE INDEX binaries_metadata_provides + ON binaries_metadata (bin_id) WHERE key_id = %d""" % key) c.execute("UPDATE config SET value = '66' WHERE name = 'db_revision'") self.db.commit() - except psycopg2.ProgrammingError, msg: + except psycopg2.ProgrammingError as msg: self.db.rollback() - raise DBUpdateError, 'Unable to apply sick update 66, rollback issued. Error message : %s' % (str(msg)) + raise DBUpdateError('Unable to apply sick update 66, rollback issued. Error message : %s' % (str(msg)))