X-Git-Url: https://git.decadent.org.uk/gitweb/?a=blobdiff_plain;f=dak%2Fdakdb%2Fupdate25.py;h=0ce8ab6e1298645203c4dad206bfe91efbc974ed;hb=1eeb90f6bf381e10fcd8f0a04437883b443855d5;hp=a61deb61352f6b1464309147655142f485b3e7be;hpb=ca19ea22806872ba8360086b121c468689fe98df;p=dak.git diff --git a/dak/dakdb/update25.py b/dak/dakdb/update25.py index a61deb61..0ce8ab6e 100644 --- a/dak/dakdb/update25.py +++ b/dak/dakdb/update25.py @@ -1,11 +1,10 @@ #!/usr/bin/env python -# coding=utf8 """ -Adding a trainee field to the process-new notes +Add views for new dominate command. @contact: Debian FTP Master -@copyright: 2009 Mike O'Connor +@copyright: 2009 Torsten Werner @license: GNU General Public License version 2 or later """ @@ -23,246 +22,168 @@ Adding a trainee field to the process-new notes # along with this program; if not, write to the Free Software # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA -################################################################################ - - -################################################################################ - import psycopg2 -import time -from daklib.dak_exceptions import DBUpdateError - -################################################################################ - -def suites(): - """ - return a list of suites to operate on - """ - if Config().has_key( "%s::%s" %(options_prefix,"Suite")): - suites = utils.split_args(Config()[ "%s::%s" %(options_prefix,"Suite")]) - else: - suites = [ 'unstable', 'testing' ] -# suites = Config().SubTree("Suite").List() - - return suites - -def arches(cursor, suite): - """ - return a list of archs to operate on - """ - arch_list = [] - cursor.execute("""SELECT s.architecture, a.arch_string - FROM suite_architectures s - JOIN architecture a ON (s.architecture=a.id) - WHERE suite = :suite""", {'suite' : suite }) - - while True: - r = cursor.fetchone() - if not r: - break - - if r[1] != "source" and r[1] != "all": - arch_list.append((r[0], r[1])) - - return arch_list def do_update(self): - """ - Adding contents table as first step to maybe, finally getting rid - of apt-ftparchive - """ - - print __doc__ + print "Add views for generate_filelist to database." try: c = self.db.cursor() - c.execute("""CREATE TABLE pending_bin_contents ( - id serial NOT NULL, - package text NOT NULL, - version debversion NOT NULL, - arch int NOT NULL, - filename text NOT NULL, - type int NOT NULL, - PRIMARY KEY(id))""" ); - - c.execute("""CREATE TABLE deb_contents ( - filename text, - section text, - package text, - binary_id integer, - arch integer, - suite integer)""" ) - - c.execute("""CREATE TABLE udeb_contents ( - filename text, - section text, - package text, - binary_id integer, - suite integer, - arch integer)""" ) - - c.execute("""ALTER TABLE ONLY deb_contents - ADD CONSTRAINT deb_contents_arch_fkey - FOREIGN KEY (arch) REFERENCES architecture(id) - ON DELETE CASCADE;""") - - c.execute("""ALTER TABLE ONLY udeb_contents - ADD CONSTRAINT udeb_contents_arch_fkey - FOREIGN KEY (arch) REFERENCES architecture(id) - ON DELETE CASCADE;""") - - c.execute("""ALTER TABLE ONLY deb_contents - ADD CONSTRAINT deb_contents_pkey - PRIMARY KEY (filename,package,arch,suite);""") - - c.execute("""ALTER TABLE ONLY udeb_contents - ADD CONSTRAINT udeb_contents_pkey - PRIMARY KEY (filename,package,arch,suite);""") - - c.execute("""ALTER TABLE ONLY deb_contents - ADD CONSTRAINT deb_contents_suite_fkey - FOREIGN KEY (suite) REFERENCES suite(id) - ON DELETE CASCADE;""") - - c.execute("""ALTER TABLE ONLY udeb_contents - ADD CONSTRAINT udeb_contents_suite_fkey - FOREIGN KEY (suite) REFERENCES suite(id) - ON DELETE CASCADE;""") - - c.execute("""ALTER TABLE ONLY deb_contents - ADD CONSTRAINT deb_contents_binary_fkey - FOREIGN KEY (binary_id) REFERENCES binaries(id) - ON DELETE CASCADE;""") - - c.execute("""ALTER TABLE ONLY udeb_contents - ADD CONSTRAINT udeb_contents_binary_fkey - FOREIGN KEY (binary_id) REFERENCES binaries(id) - ON DELETE CASCADE;""") - - c.execute("""CREATE INDEX ind_deb_contents_binary ON deb_contents(binary_id);""" ) - - - suites = self.suites() - - for suite in [i.lower() for i in suites]: - suite_id = DBConn().get_suite_id(suite) - arch_list = arches(c, suite_id) - arch_list = arches(c, suite_id) - - for (arch_id,arch_str) in arch_list: - c.execute( "CREATE INDEX ind_deb_contents_%s_%s ON deb_contents (arch,suite) WHERE (arch=2 OR arch=%d) AND suite=$d"%(arch_str,suite,arch_id,suite_id) ) - - for section, sname in [("debian-installer","main"), - ("non-free/debian-installer", "nonfree")]: - c.execute( "CREATE INDEX ind_udeb_contents_%s_%s ON udeb_contents (section,suite) WHERE section=%s AND suite=$d"%(sname,suite,section,suite_id) ) - - - c.execute( """CREATE OR REPLACE FUNCTION update_contents_for_bin_a() RETURNS trigger AS $$ - event = TD["event"] - if event == "DELETE" or event == "UPDATE": - - plpy.execute(plpy.prepare("DELETE FROM deb_contents WHERE binary_id=$1 and suite=$2", - ["int","int"]), - [TD["old"]["bin"], TD["old"]["suite"]]) - - if event == "INSERT" or event == "UPDATE": - - content_data = plpy.execute(plpy.prepare( - """SELECT s.section, b.package, b.architecture, ot.type - FROM override o - JOIN override_type ot on o.type=ot.id - JOIN binaries b on b.package=o.package - JOIN files f on b.file=f.id - JOIN location l on l.id=f.location - JOIN section s on s.id=o.section - WHERE b.id=$1 - AND o.suite=$2 - """, - ["int", "int"]), - [TD["new"]["bin"], TD["new"]["suite"]])[0] - - tablename="%s_contents" % content_data['type'] - - plpy.execute(plpy.prepare("""DELETE FROM %s - WHERE package=$1 and arch=$2 and suite=$3""" % tablename, - ['text','int','int']), - [content_data['package'], - content_data['architecture'], - TD["new"]["suite"]]) - - filenames = plpy.execute(plpy.prepare( - "SELECT bc.file FROM bin_contents bc where bc.binary_id=$1", - ["int"]), - [TD["new"]["bin"]]) - - for filename in filenames: - plpy.execute(plpy.prepare( - """INSERT INTO %s - (filename,section,package,binary_id,arch,suite) - VALUES($1,$2,$3,$4,$5,$6)""" % tablename, - ["text","text","text","int","int","int"]), - [filename["file"], - content_data["section"], - content_data["package"], - TD["new"]["bin"], - content_data["architecture"], - TD["new"]["suite"]] ) -$$ LANGUAGE plpythonu VOLATILE SECURITY DEFINER; -""") - - - c.execute( """CREATE OR REPLACE FUNCTION update_contents_for_override() RETURNS trigger AS $$ - event = TD["event"] - if event == "UPDATE": - - otype = plpy.execute(plpy.prepare("SELECT type from override_type where id=$1",["int"]),[TD["new"]["type"]] )[0]; - if otype["type"].endswith("deb"): - section = plpy.execute(plpy.prepare("SELECT section from section where id=$1",["int"]),[TD["new"]["section"]] )[0]; - - table_name = "%s_contents" % otype["type"] - plpy.execute(plpy.prepare("UPDATE %s set section=$1 where package=$2 and suite=$3" % table_name, - ["text","text","int"]), - [section["section"], - TD["new"]["package"], - TD["new"]["suite"]]) - -$$ LANGUAGE plpythonu VOLATILE SECURITY DEFINER; -""") - - c.execute("""CREATE OR REPLACE FUNCTION update_contents_for_override() - RETURNS trigger AS $$ - event = TD["event"] - if event == "UPDATE" or event == "INSERT": - row = TD["new"] - r = plpy.execute(plpy.prepare( """SELECT 1 from suite_architectures sa - JOIN binaries b ON b.architecture = sa.architecture - WHERE b.id = $1 and sa.suite = $2""", - ["int", "int"]), - [row["bin"], row["suite"]]) - if not len(r): - plpy.error("Illegal architecture for this suite") - -$$ LANGUAGE plpythonu VOLATILE;""") - - c.execute( """CREATE TRIGGER illegal_suite_arch_bin_associations_trigger - BEFORE INSERT OR UPDATE ON bin_associations - FOR EACH ROW EXECUTE PROCEDURE update_contents_for_override();""") - - c.execute( """CREATE TRIGGER bin_associations_contents_trigger - AFTER INSERT OR UPDATE OR DELETE ON bin_associations - FOR EACH ROW EXECUTE PROCEDURE update_contents_for_bin_a();""") - c.execute("""CREATE TRIGGER override_contents_trigger - AFTER UPDATE ON override - FOR EACH ROW EXECUTE PROCEDURE update_contents_for_override();""") - - - c.execute( "CREATE INDEX ind_deb_contents_name ON deb_contents(package);"); - c.execute( "CREATE INDEX ind_udeb_contents_name ON udeb_contents(package);"); - + print "Drop old views." + c.execute("DROP VIEW IF EXISTS binaries_suite_arch CASCADE") + c.execute("DROP VIEW IF EXISTS newest_all_associations CASCADE") + c.execute("DROP VIEW IF EXISTS obsolete_any_by_all_associations CASCADE") + c.execute("DROP VIEW IF EXISTS newest_any_associations CASCADE") + c.execute("DROP VIEW IF EXISTS obsolete_any_associations CASCADE") + c.execute("DROP VIEW IF EXISTS source_suite CASCADE") + c.execute("DROP VIEW IF EXISTS newest_source CASCADE") + c.execute("DROP VIEW IF EXISTS newest_src_association CASCADE") + c.execute("DROP VIEW IF EXISTS any_associations_source CASCADE") + c.execute("DROP VIEW IF EXISTS src_associations_src CASCADE") + c.execute("DROP VIEW IF EXISTS almost_obsolete_src_associations CASCADE") + c.execute("DROP VIEW IF EXISTS obsolete_src_associations CASCADE") + c.execute("DROP VIEW IF EXISTS bin_associations_binaries CASCADE") + c.execute("DROP VIEW IF EXISTS src_associations_bin CASCADE") + c.execute("DROP VIEW IF EXISTS almost_obsolete_all_associations CASCADE") + c.execute("DROP VIEW IF EXISTS obsolete_all_associations CASCADE") + + print "Create new views." + c.execute(""" +CREATE VIEW binaries_suite_arch AS + SELECT bin_associations.id, binaries.id AS bin, binaries.package, + binaries.version, binaries.source, bin_associations.suite, + suite.suite_name, binaries.architecture, architecture.arch_string + FROM binaries JOIN bin_associations ON binaries.id = bin_associations.bin + JOIN suite ON suite.id = bin_associations.suite + JOIN architecture ON binaries.architecture = architecture.id; + """) + c.execute(""" +CREATE VIEW newest_all_associations AS + SELECT package, max(version) AS version, suite, architecture + FROM binaries_suite_arch + WHERE architecture = 2 GROUP BY package, suite, architecture; + """) + c.execute(""" +CREATE VIEW obsolete_any_by_all_associations AS + SELECT binaries_suite_arch.id, binaries_suite_arch.package, + binaries_suite_arch.version, binaries_suite_arch.suite, + binaries_suite_arch.architecture + FROM binaries_suite_arch + JOIN newest_all_associations + ON (binaries_suite_arch.package = newest_all_associations.package AND + binaries_suite_arch.version < newest_all_associations.version AND + binaries_suite_arch.suite = newest_all_associations.suite AND + binaries_suite_arch.architecture > 2); + """) + c.execute(""" +CREATE VIEW newest_any_associations AS + SELECT package, max(version) AS version, suite, architecture + FROM binaries_suite_arch + WHERE architecture > 2 GROUP BY package, suite, architecture; + """) + c.execute(""" +CREATE VIEW obsolete_any_associations AS + SELECT id, binaries_suite_arch.architecture, binaries_suite_arch.version, + binaries_suite_arch.package, binaries_suite_arch.suite + FROM binaries_suite_arch + JOIN newest_any_associations + ON binaries_suite_arch.architecture = newest_any_associations.architecture AND + binaries_suite_arch.package = newest_any_associations.package AND + binaries_suite_arch.suite = newest_any_associations.suite AND + binaries_suite_arch.version != newest_any_associations.version; + """) + c.execute(""" +CREATE VIEW source_suite AS + SELECT src_associations.id, source.id AS src , source.source, source.version, + src_associations.suite, suite.suite_name + FROM source + JOIN src_associations ON source.id = src_associations.source + JOIN suite ON suite.id = src_associations.suite; + """) + c.execute(""" +CREATE VIEW newest_source AS + SELECT source, max(version) AS version, suite + FROM source_suite + GROUP BY source, suite; + """) + c.execute(""" +CREATE VIEW newest_src_association AS + SELECT id, src, source, version, suite + FROM source_suite + JOIN newest_source USING (source, version, suite); + """) + c.execute(""" +CREATE VIEW any_associations_source AS + SELECT bin_associations.id, bin_associations.suite, binaries.id AS bin, + binaries.package, binaries.version AS binver, binaries.architecture, + source.id AS src, source.source, source.version AS srcver + FROM bin_associations + JOIN binaries ON bin_associations.bin = binaries.id AND architecture != 2 + JOIN source ON binaries.source = source.id; + """) + c.execute(""" +CREATE VIEW src_associations_src AS + SELECT src_associations.id, src_associations.suite, source.id AS src, + source.source, source.version + FROM src_associations + JOIN source ON src_associations.source = source.id; + """) + c.execute(""" +CREATE VIEW almost_obsolete_src_associations AS + SELECT src_associations_src.id, src_associations_src.src, + src_associations_src.source, src_associations_src.version, suite + FROM src_associations_src + LEFT JOIN any_associations_source USING (src, suite) + WHERE bin IS NULL; + """) + c.execute(""" +CREATE VIEW obsolete_src_associations AS + SELECT almost.id, almost.src, almost.source, almost.version, almost.suite + FROM almost_obsolete_src_associations as almost + JOIN newest_src_association AS newest + ON almost.source = newest.source AND + almost.version < newest.version AND + almost.suite = newest.suite; + """) + c.execute(""" +CREATE VIEW bin_associations_binaries AS + SELECT bin_associations.id, bin_associations.bin, binaries.package, + binaries.version, bin_associations.suite, binaries.architecture + FROM bin_associations + JOIN binaries ON bin_associations.bin = binaries.id; + """) + c.execute(""" +CREATE VIEW src_associations_bin AS + SELECT src_associations.id, src_associations.source, src_associations.suite, + binaries.id AS bin, binaries.architecture + FROM src_associations + JOIN source ON src_associations.source = source.id + JOIN binaries ON source.id = binaries.source; + """) + c.execute(""" +CREATE VIEW almost_obsolete_all_associations AS + SELECT bin_associations_binaries.id AS id, bin, bin_associations_binaries.package, + bin_associations_binaries.version, suite + FROM bin_associations_binaries + LEFT JOIN src_associations_bin USING (bin, suite, architecture) + WHERE source IS NULL AND architecture = 2; + """) + c.execute(""" +CREATE VIEW obsolete_all_associations AS + SELECT almost.id, almost.bin, almost.package, almost.version, almost.suite + FROM almost_obsolete_all_associations AS almost + JOIN newest_all_associations AS newest + ON almost.package = newest.package AND + almost.version < newest.version AND + almost.suite = newest.suite; + """) + + print "Committing" + c.execute("UPDATE config SET value = '25' WHERE name = 'db_revision'") self.db.commit() - except psycopg2.ProgrammingError, msg: + except psycopg2.InternalError as msg: self.db.rollback() - raise DBUpdateError, "Unable to apply process-new update 14, rollback issued. Error message : %s" % (str(msg)) + raise DBUpdateError("Database error, rollback issued. Error message : %s" % (str(msg)))