Freeze Break Request: Fedora Tagger Schema

[Ralph Bean]

This is a second freeze break request for Fedora Tagger.  I haven't
actually carried out the first one yet, but Kevin's mention of the
broken bodhi sqlitebuildtags url took us down a rabbit hole.

This is the traceback that tagger logs when the bodhi masher tries to
pull down its sqlitebuildtags:

   Traceback (most recent call last):
     File "/usr/lib/python2.6/site-packages/flask/app.py", line 1687, in wsgi_app
       response = self.full_dispatch_request()
     File "/usr/lib/python2.6/site-packages/flask/app.py", line 1360, in full_dispatch_request
       rv = self.handle_user_exception(e)
     File "/usr/lib/python2.6/site-packages/flask/app.py", line 1358, in full_dispatch_request
       rv = self.dispatch_request()
     File "/usr/lib/python2.6/site-packages/flask/app.py", line 1344, in dispatch_request
       return self.view_functions[rule.endpoint](**req.view_args)
     File "/usr/lib/python2.6/site-packages/fedoratagger/api/api.py", line 461, in tag_pkg_sqlite
       return fedoratagger.lib.sqlitebuildtags()
     File "/usr/lib/python2.6/site-packages/fedoratagger/lib/sqlite_export.py", line 56, in sqlitebuildtags
       conn.executemany(insert_statement, rows)
   IntegrityError: columns name, tag are not unique

The problem is that the schema upgrade from tagger1 to tagger2 didn't
go smoothly in production, and we didn't notice.  There is supposed to
be a DB constraint that disallows duplicate tags on packages, but it is
absent.

We can't just apply that new constraint, because there are now two
duplicate tags in the DB.

We need to:

  1) Remove the duplicates
  2) Apply the constraint so it doesn't happen again

Here's the constraint we need to apply in postgres:

    ALTER TABLE tag ADD CONSTRAINT unique_package_label UNIQUE (package_id, label);

Here's a script to remove the duplicates:

    #!/usr/bin/env python

    import os
    os.environ['FEDORATAGGER_CONFIG'] = '/etc/fedoratagger/fedoratagger.cfg'

    from sqlalchemy import func, and_
    import fedoratagger
    import fedoratagger.lib.model as m

    print 'Looking now for package tag dupes.'
    query = fedoratagger.SESSION.query(m.Tag).all()
    results = {}

    for tag in query:
        results[tag.label] = results.get(tag.label, {})
        results[tag.label][tag.package_id] = \
            results[tag.label].get(tag.package_id, 0) + 1 

    dupes = []
    for label, packages in results.items():
        for package, count in packages.items():
            if count > 1:
                dupes.append((label, package))

    print "Found these package+tag dupes:", dupes

    base_query = fedoratagger.SESSION.query(m.Tag)
    for label, package_id in dupes:
        query = base_query.filter(and_(
            m.Tag.label==label,
            m.Tag.package_id==package_id
        )).all()

        keep, rest = query[0], query[1:]
        for dupe in rest:
            fedoratagger.SESSION.delete(dupe)

    fedoratagger.SESSION.commit()
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 490 bytes
Desc: not available
URL: <http://lists.fedoraproject.org/pipermail/infrastructure/attachments/20130516/a21230d6/attachment.sig>