From: Ansgar Burchardt Date: Tue, 6 Sep 2011 18:20:27 +0000 (+0000) Subject: Move query for add-missing-source-checksums to Python code X-Git-Url: https://git.decadent.org.uk/gitweb/?p=dak.git;a=commitdiff_plain;h=8eef1cfc770d01b357ac3a413f3ccd94c30065f7 Move query for add-missing-source-checksums to Python code There is no real need for using a stored procedure and having the query in the normal code makes future changes easier. --- diff --git a/dak/check_archive.py b/dak/check_archive.py index 3e537d80..e5425226 100755 --- a/dak/check_archive.py +++ b/dak/check_archive.py @@ -479,11 +479,40 @@ def check_build_depends(): ################################################################################ +_add_missing_source_checksums_query = R""" +INSERT INTO source_metadata + (src_id, key_id, value) +SELECT + s.id, + :checksum_key, + E'\n' || + (SELECT STRING_AGG(' ' || tmp.checksum || ' ' || tmp.size || ' ' || tmp.basename, E'\n' ORDER BY tmp.basename) + FROM + (SELECT + CASE :checksum_type + WHEN 'Files' THEN f.md5sum + WHEN 'Checksums-Sha1' THEN f.sha1sum + WHEN 'Checksums-Sha256' THEN f.sha256sum + END AS checksum, + f.size, + SUBSTRING(f.filename FROM E'/([^/]*)\\Z') AS basename + FROM files f JOIN dsc_files ON f.id = dsc_files.file + WHERE dsc_files.source = s.id AND f.id != s.file + ) AS tmp + ) + + FROM + source s + WHERE NOT EXISTS (SELECT 1 FROM source_metadata md WHERE md.src_id=s.id AND md.key_id = :checksum_key); +""" + def add_missing_source_checksums(): """ Add missing source checksums to source_metadata """ session = DBConn().session() for checksum in ['Files', 'Checksums-Sha1', 'Checksums-Sha256']: - rows = session.execute('SELECT source_metadata_add_missing_checksum(:type)', {'type': checksum}).scalar() + checksum_key = get_or_set_metadatakey(checksum, session).key_id + rows = session.execute(_add_missing_source_checksums_query, + {'checksum_key': checksum_key, 'checksum_type': checksum}).rowcount if rows > 0: print "Added {0} missing entries for {1}".format(rows, checksum) session.commit() diff --git a/dak/dakdb/update71.py b/dak/dakdb/update71.py deleted file mode 100755 index 079c195c..00000000 --- a/dak/dakdb/update71.py +++ /dev/null @@ -1,115 +0,0 @@ -#!/usr/bin/env python -# coding=utf8 - -""" -Add missing checksums to source_metadata - -@contact: Debian FTP Master -@copyright: 2011 Ansgar Burchardt -@license: GNU General Public License version 2 or later -""" - -# This program is free software; you can redistribute it and/or modify -# it under the terms of the GNU General Public License as published by -# the Free Software Foundation; either version 2 of the License, or -# (at your option) any later version. - -# This program is distributed in the hope that it will be useful, -# but WITHOUT ANY WARRANTY; without even the implied warranty of -# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -# GNU General Public License for more details. - -# You should have received a copy of the GNU General Public License -# 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 -from daklib.dak_exceptions import DBUpdateError -from daklib.config import Config - -################################################################################ -def do_update(self): - """ - Add missing checksums to source_metadata - """ - print __doc__ - try: - c = self.db.cursor() - c.execute(R"""CREATE OR REPLACE FUNCTION metadata_keys_get(key_ text) - RETURNS integer - LANGUAGE plpgsql - STRICT -AS $function$ -DECLARE - v_key_id metadata_keys.key_id%TYPE; -BEGIN - SELECT key_id INTO v_key_id FROM metadata_keys WHERE key = key_; - IF NOT FOUND THEN - INSERT INTO metadata_keys (key) VALUES (key_) RETURNING key_id INTO v_key_id; - END IF; - RETURN v_key_id; -END; -$function$ -""") - - c.execute("""COMMENT ON FUNCTION metadata_keys_get(text) -IS 'return key_id for the given key. If key is not present, create a new entry.' -""") - - c.execute(R"""CREATE OR REPLACE FUNCTION source_metadata_add_missing_checksum(type text) - RETURNS INTEGER - LANGUAGE plpgsql - STRICT -AS $function$ -DECLARE - v_checksum_key metadata_keys.key_id%TYPE; - rows INTEGER; -BEGIN - IF type NOT IN ('Files', 'Checksums-Sha1', 'Checksums-Sha256') THEN - RAISE EXCEPTION 'Unknown checksum field %', type; - END IF; - v_checksum_key := metadata_keys_get(type); - - INSERT INTO source_metadata - (src_id, key_id, value) - SELECT - s.id, - v_checksum_key, - E'\n' || - (SELECT STRING_AGG(' ' || tmp.checksum || ' ' || tmp.size || ' ' || tmp.basename, E'\n' ORDER BY tmp.basename) - FROM - (SELECT - CASE type - WHEN 'Files' THEN f.md5sum - WHEN 'Checksums-Sha1' THEN f.sha1sum - WHEN 'Checksums-Sha256' THEN f.sha256sum - END AS checksum, - f.size, - SUBSTRING(f.filename FROM E'/([^/]*)\\Z') AS basename - FROM files f JOIN dsc_files ON f.id = dsc_files.file - WHERE dsc_files.source = s.id AND f.id != s.file - ) AS tmp - ) - - FROM - source s - WHERE NOT EXISTS (SELECT 1 FROM source_metadata md WHERE md.src_id=s.id AND md.key_id = v_checksum_key); - - GET DIAGNOSTICS rows = ROW_COUNT; - RETURN rows; -END; -$function$ -""") - - c.execute("""COMMENT ON FUNCTION source_metadata_add_missing_checksum(TEXT) -IS 'add missing checksum fields to source_metadata. type can be Files (md5sum), Checksums-Sha1, Checksums-Sha256' -""") - - c.execute("UPDATE config SET value = '71' WHERE name = 'db_revision'") - self.db.commit() - - except psycopg2.ProgrammingError as msg: - self.db.rollback() - raise DBUpdateError('Unable to apply sick update 71, rollback issued. Error message : %s' % (str(msg))) diff --git a/dak/update_db.py b/dak/update_db.py index 2a946822..3e31a3be 100755 --- a/dak/update_db.py +++ b/dak/update_db.py @@ -46,7 +46,7 @@ from daklib.daklog import Logger ################################################################################ Cnf = None -required_database_schema = 71 +required_database_schema = 70 ################################################################################