[dak/master] 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.
---
dak/check_archive.py | 31 +++++++++++++-
dak/dakdb/update71.py | 115 -------------------------------------------------
dak/update_db.py | 2 +-
3 files changed, 31 insertions(+), 117 deletions(-)
delete mode 100755 dak/dakdb/update71.py
diff --git a/dak/check_archive.py b/dak/check_archive.py
index 3e537d8..e542522 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 079c195..0000000
--- 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 <ftpmaster@debian.org>
-@copyright: 2011 Ansgar Burchardt <ansgar@debian.org>
-@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 2a94682..3e31a3b 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
################################################################################
--
1.7.2.5
Reply to: