]> git.decadent.org.uk Git - dak.git/blobdiff - daklib/dbconn.py
Test and refactor get_suites_source_in().
[dak.git] / daklib / dbconn.py
index 5f45cdfc9f8ee1763ef3ab2f3c40337c36d4a577..6f598bb5dd5597c061395bbceff55a6a4c8a6334 100755 (executable)
@@ -37,6 +37,7 @@ import os
 import re
 import psycopg2
 import traceback
+import commands
 from datetime import datetime, timedelta
 from errno import ENOENT
 from tempfile import mkstemp, mkdtemp
@@ -44,8 +45,8 @@ from tempfile import mkstemp, mkdtemp
 from inspect import getargspec
 
 import sqlalchemy
-from sqlalchemy import create_engine, Table, MetaData
-from sqlalchemy.orm import sessionmaker, mapper, relation
+from sqlalchemy import create_engine, Table, MetaData, Column, Integer
+from sqlalchemy.orm import sessionmaker, mapper, relation, object_session, backref
 from sqlalchemy import types as sqltypes
 
 # Don't remove this, we re-export the exceptions to scripts which import us
@@ -56,30 +57,51 @@ from sqlalchemy.orm.exc import NoResultFound
 # in the database
 from config import Config
 from textutils import fix_maintainer
+from dak_exceptions import NoSourceFieldError
+
+# suppress some deprecation warnings in squeeze related to sqlalchemy
+import warnings
+warnings.filterwarnings('ignore', \
+    "The SQLAlchemy PostgreSQL dialect has been renamed from 'postgres' to 'postgresql'.*", \
+    SADeprecationWarning)
+# TODO: sqlalchemy needs some extra configuration to correctly reflect
+# the ind_deb_contents_* indexes - we ignore the warnings at the moment
+warnings.filterwarnings("ignore", 'Predicate of partial index', SAWarning)
+
 
 ################################################################################
 
 # Patch in support for the debversion field type so that it works during
 # reflection
 
-class DebVersion(sqltypes.Text):
-    """
-    Support the debversion type
-    """
+try:
+    # that is for sqlalchemy 0.6
+    UserDefinedType = sqltypes.UserDefinedType
+except:
+    # this one for sqlalchemy 0.5
+    UserDefinedType = sqltypes.TypeEngine
 
+class DebVersion(UserDefinedType):
     def get_col_spec(self):
         return "DEBVERSION"
 
+    def bind_processor(self, dialect):
+        return None
+
+    # ' = None' is needed for sqlalchemy 0.5:
+    def result_processor(self, dialect, coltype = None):
+        return None
+
 sa_major_version = sqlalchemy.__version__[0:3]
-if sa_major_version == "0.5":
+if sa_major_version in ["0.5", "0.6"]:
     from sqlalchemy.databases import postgres
     postgres.ischema_names['debversion'] = DebVersion
 else:
-    raise Exception("dak isn't ported to SQLA versions != 0.5 yet.  See daklib/dbconn.py")
+    raise Exception("dak only ported to SQLA versions 0.5 and 0.6.  See daklib/dbconn.py")
 
 ################################################################################
 
-__all__ = ['IntegrityError', 'SQLAlchemyError']
+__all__ = ['IntegrityError', 'SQLAlchemyError', 'DebVersion']
 
 ################################################################################
 
@@ -135,8 +157,9 @@ __all__.append('session_wrapper')
 ################################################################################
 
 class Architecture(object):
-    def __init__(self, *args, **kwargs):
-        pass
+    def __init__(self, arch_string = None, description = None):
+        self.arch_string = arch_string
+        self.description = description
 
     def __eq__(self, val):
         if isinstance(val, str):
@@ -180,6 +203,7 @@ def get_architecture(architecture, session=None):
 
 __all__.append('get_architecture')
 
+# TODO: should be removed because the implementation is too trivial
 @session_wrapper
 def get_architecture_suites(architecture, session=None):
     """
@@ -196,13 +220,7 @@ def get_architecture_suites(architecture, session=None):
     @return: list of Suite objects for the given name (may be empty)
     """
 
-    q = session.query(Suite)
-    q = q.join(SuiteArchitecture)
-    q = q.join(Architecture).filter_by(arch_string=architecture).order_by('suite_name')
-
-    ret = q.all()
-
-    return ret
+    return get_architecture(architecture, session).suites
 
 __all__.append('get_architecture_suites')
 
@@ -441,8 +459,8 @@ MINIMAL_APT_CONF="""
 Dir
 {
    ArchiveDir "%(archivepath)s";
-   OverrideDir "/srv/ftp.debian.org/scripts/override/";
-   CacheDir "/srv/ftp.debian.org/database/";
+   OverrideDir "%(overridedir)s";
+   CacheDir "%(cachedir)s";
 };
 
 Default
@@ -504,11 +522,16 @@ class BuildQueue(object):
                 os.write(fl_fd, '%s\n' % n.fullpath)
             os.close(fl_fd)
 
+            cnf = Config()
+
             # Write minimal apt.conf
             # TODO: Remove hardcoding from template
             (ac_fd, ac_name) = mkstemp()
             os.write(ac_fd, MINIMAL_APT_CONF % {'archivepath': self.path,
-                                                'filelist': fl_name})
+                                                'filelist': fl_name,
+                                                'cachedir': cnf["Dir::Cache"],
+                                                'overridedir': cnf["Dir::Override"],
+                                                })
             os.close(ac_fd)
 
             # Run apt-ftparchive generate
@@ -538,7 +561,6 @@ class BuildQueue(object):
 
             # Sign if necessary
             if self.signingkey:
-                cnf = Config()
                 keyring = "--secret-keyring \"%s\"" % cnf["Dinstall::SigningKeyring"]
                 if cnf.has_key("Dinstall::SigningPubKeyring"):
                     keyring += " --keyring \"%s\"" % cnf["Dinstall::SigningPubKeyring"]
@@ -610,7 +632,7 @@ class BuildQueue(object):
         session.commit()
 
         for f in os.listdir(self.path):
-            if f.startswith('Packages') or f.startswith('Source') or f.startswith('Release'):
+            if f.startswith('Packages') or f.startswith('Source') or f.startswith('Release') or f.startswith('advisory'):
                 continue
 
             try:
@@ -1048,8 +1070,12 @@ __all__.append('get_dscfiles')
 ################################################################################
 
 class PoolFile(object):
-    def __init__(self, *args, **kwargs):
-        pass
+    def __init__(self, filename = None, location = None, filesize = -1, \
+        md5sum = None):
+        self.filename = filename
+        self.location = location
+        self.filesize = filesize
+        self.md5sum = md5sum
 
     def __repr__(self):
         return '<PoolFile %s>' % self.filename
@@ -1058,13 +1084,16 @@ class PoolFile(object):
     def fullpath(self):
         return os.path.join(self.location.path, self.filename)
 
+    def is_valid(self, filesize = -1, md5sum = None):\
+        return self.filesize == filesize and self.md5sum == md5sum
+
 __all__.append('PoolFile')
 
 @session_wrapper
 def check_poolfile(filename, filesize, md5sum, location_id, session=None):
     """
     Returns a tuple:
-    (ValidFileFound [boolean or None], PoolFile object or None)
+    (ValidFileFound [boolean], PoolFile object or None)
 
     @type filename: string
     @param filename: the filename of the file to check against the DB
@@ -1080,34 +1109,24 @@ def check_poolfile(filename, filesize, md5sum, location_id, session=None):
 
     @rtype: tuple
     @return: Tuple of length 2.
-                 - If more than one file found with that name: (C{None},  C{None})
                  - If valid pool file found: (C{True}, C{PoolFile object})
                  - If valid pool file not found:
                      - (C{False}, C{None}) if no file found
                      - (C{False}, C{PoolFile object}) if file found with size/md5sum mismatch
     """
 
-    q = session.query(PoolFile).filter_by(filename=filename)
-    q = q.join(Location).filter_by(location_id=location_id)
-
-    ret = None
-
-    if q.count() > 1:
-        ret = (None, None)
-    elif q.count() < 1:
-        ret = (False, None)
-    else:
-        obj = q.one()
-        if obj.md5sum != md5sum or obj.filesize != int(filesize):
-            ret = (False, obj)
+    poolfile = session.query(Location).get(location_id). \
+        files.filter_by(filename=filename).first()
+    valid = False
+    if poolfile and poolfile.is_valid(filesize = filesize, md5sum = md5sum):
+        valid = True
 
-    if ret is None:
-        ret = (True, obj)
-
-    return ret
+    return (valid, poolfile)
 
 __all__.append('check_poolfile')
 
+# TODO: the implementation can trivially be inlined at the place where the
+# function is called
 @session_wrapper
 def get_poolfile_by_id(file_id, session=None):
     """
@@ -1120,41 +1139,10 @@ def get_poolfile_by_id(file_id, session=None):
     @return: either the PoolFile object or None
     """
 
-    q = session.query(PoolFile).filter_by(file_id=file_id)
-
-    try:
-        return q.one()
-    except NoResultFound:
-        return None
+    return session.query(PoolFile).get(file_id)
 
 __all__.append('get_poolfile_by_id')
 
-
-@session_wrapper
-def get_poolfile_by_name(filename, location_id=None, session=None):
-    """
-    Returns an array of PoolFile objects for the given filename and
-    (optionally) location_id
-
-    @type filename: string
-    @param filename: the filename of the file to check against the DB
-
-    @type location_id: int
-    @param location_id: the id of the location to look in (optional)
-
-    @rtype: array
-    @return: array of PoolFile objects
-    """
-
-    q = session.query(PoolFile).filter_by(filename=filename)
-
-    if location_id is not None:
-        q = q.join(Location).filter_by(location_id=location_id)
-
-    return q.all()
-
-__all__.append('get_poolfile_by_name')
-
 @session_wrapper
 def get_poolfile_like_name(filename, session=None):
     """
@@ -1210,8 +1198,8 @@ __all__.append('add_poolfile')
 ################################################################################
 
 class Fingerprint(object):
-    def __init__(self, *args, **kwargs):
-        pass
+    def __init__(self, fingerprint = None):
+        self.fingerprint = fingerprint
 
     def __repr__(self):
         return '<Fingerprint %s>' % self.fingerprint
@@ -1312,9 +1300,17 @@ class Keyring(object):
             esclist[x] = "%c" % (int(esclist[x][2:],16))
         return "".join(esclist)
 
-    def load_keys(self, keyring):
+    def parse_address(self, uid):
+        """parses uid and returns a tuple of real name and email address"""
         import email.Utils
+        (name, address) = email.Utils.parseaddr(uid)
+        name = re.sub(r"\s*[(].*[)]", "", name)
+        name = self.de_escape_gpg_str(name)
+        if name == "":
+            name = uid
+        return (name, address)
 
+    def load_keys(self, keyring):
         if not self.keyring_id:
             raise Exception('Must be initialized with database information')
 
@@ -1326,24 +1322,20 @@ class Keyring(object):
             field = line.split(":")
             if field[0] == "pub":
                 key = field[4]
-                (name, addr) = email.Utils.parseaddr(field[9])
-                name = re.sub(r"\s*[(].*[)]", "", name)
-                if name == "" or addr == "" or "@" not in addr:
-                    name = field[9]
-                    addr = "invalid-uid"
-                name = self.de_escape_gpg_str(name)
-                self.keys[key] = {"email": addr}
-                if name != "":
+                self.keys[key] = {}
+                (name, addr) = self.parse_address(field[9])
+                if "@" in addr:
+                    self.keys[key]["email"] = addr
                     self.keys[key]["name"] = name
-                self.keys[key]["aliases"] = [name]
                 self.keys[key]["fingerprints"] = []
                 signingkey = True
             elif key and field[0] == "sub" and len(field) >= 12:
                 signingkey = ("s" in field[11])
             elif key and field[0] == "uid":
-                (name, addr) = email.Utils.parseaddr(field[9])
-                if name and name not in self.keys[key]["aliases"]:
-                    self.keys[key]["aliases"].append(name)
+                (name, addr) = self.parse_address(field[9])
+                if "email" not in self.keys[key] and "@" in addr:
+                    self.keys[key]["email"] = addr
+                    self.keys[key]["name"] = name
             elif signingkey and field[0] == "fpr":
                 self.keys[key]["fingerprints"].append(field[9])
                 self.fpr_lookup[field[9]] = key
@@ -1391,7 +1383,7 @@ class Keyring(object):
         byname = {}
         any_invalid = False
         for x in self.keys.keys():
-            if self.keys[x]["email"] == "invalid-uid":
+            if "email" not in self.keys[x]:
                 any_invalid = True
                 self.keys[x]["uid"] = format % "invalid-uid"
             else:
@@ -1496,8 +1488,10 @@ __all__.append('get_dbchange')
 ################################################################################
 
 class Location(object):
-    def __init__(self, *args, **kwargs):
-        pass
+    def __init__(self, path = None):
+        self.path = path
+        # the column 'type' should go away, see comment at mapper
+        self.archive_type = 'pool'
 
     def __repr__(self):
         return '<Location %s (%s)>' % (self.path, self.location_id)
@@ -1511,7 +1505,7 @@ def get_location(location, component=None, archive=None, session=None):
     and archive
 
     @type location: string
-    @param location: the path of the location, e.g. I{/srv/ftp.debian.org/ftp/pool/}
+    @param location: the path of the location, e.g. I{/srv/ftp-master.debian.org/ftp/pool/}
 
     @type component: string
     @param component: the component name (if None, no restriction applied)
@@ -1541,8 +1535,8 @@ __all__.append('get_location')
 ################################################################################
 
 class Maintainer(object):
-    def __init__(self, *args, **kwargs):
-        pass
+    def __init__(self, name = None):
+        self.name = name
 
     def __repr__(self):
         return '''<Maintainer '%s' (%s)>''' % (self.name, self.maintainer_id)
@@ -1912,6 +1906,31 @@ def get_policy_queue(queuename, session=None):
 
 __all__.append('get_policy_queue')
 
+@session_wrapper
+def get_policy_queue_from_path(pathname, session=None):
+    """
+    Returns PolicyQueue object for given C{path name}
+
+    @type queuename: string
+    @param queuename: The path
+
+    @type session: Session
+    @param session: Optional SQLA session object (a temporary one will be
+    generated if not supplied)
+
+    @rtype: PolicyQueue
+    @return: PolicyQueue object for the given queue
+    """
+
+    q = session.query(PolicyQueue).filter_by(path=pathname)
+
+    try:
+        return q.one()
+    except NoResultFound:
+        return None
+
+__all__.append('get_policy_queue_from_path')
+
 ################################################################################
 
 class Priority(object):
@@ -2055,8 +2074,14 @@ __all__.append('get_sections')
 ################################################################################
 
 class DBSource(object):
-    def __init__(self, *args, **kwargs):
-        pass
+    def __init__(self, source = None, version = None, maintainer = None, \
+        changedby = None, poolfile = None, install_date = None):
+        self.source = source
+        self.version = version
+        self.maintainer = maintainer
+        self.changedby = changedby
+        self.poolfile = poolfile
+        self.install_date = install_date
 
     def __repr__(self):
         return '<DBSource %s (%s)>' % (self.source, self.version)
@@ -2143,7 +2168,7 @@ def get_suites_source_in(source, session=None):
     @return: list of Suite objects for the given source
     """
 
-    return session.query(Suite).join(SrcAssociation).join(DBSource).filter_by(source=source).all()
+    return session.query(Suite).filter(Suite.sources.any(source=source)).all()
 
 __all__.append('get_suites_source_in')
 
@@ -2182,10 +2207,12 @@ def get_sources_from_name(source, version=None, dm_upload_allowed=None, session=
 
 __all__.append('get_sources_from_name')
 
+# FIXME: This function fails badly if it finds more than 1 source package and
+# its implementation is trivial enough to be inlined.
 @session_wrapper
 def get_source_in_suite(source, suite, session=None):
     """
-    Returns list of DBSource objects for a combination of C{source} and C{suite}.
+    Returns a DBSource object for a combination of C{source} and C{suite}.
 
       - B{source} - source package name, eg. I{mailfilter}, I{bbdb}, I{glibc}
       - B{suite} - a suite name, eg. I{unstable}
@@ -2201,12 +2228,9 @@ def get_source_in_suite(source, suite, session=None):
 
     """
 
-    q = session.query(SrcAssociation)
-    q = q.join('source').filter_by(source=source)
-    q = q.join('suite').filter_by(suite_name=suite)
-
+    q = get_suite(suite, session).get_sources(source)
     try:
-        return q.one().source
+        return q.one()
     except NoResultFound:
         return None
 
@@ -2305,7 +2329,7 @@ def add_dsc_to_db(u, filename, session=None):
     # Add the src_uploaders to the DB
     uploader_ids = [source.maintainer_id]
     if u.pkg.dsc.has_key("uploaders"):
-        for up in u.pkg.dsc["uploaders"].replace(">, ", "\t")split("\t"):
+        for up in u.pkg.dsc["uploaders"].replace(">, ", ">\t").split("\t"):
             up = up.strip()
             uploader_ids.append(get_or_set_maintainer(up, session).maintainer_id)
 
@@ -2364,7 +2388,7 @@ def add_deb_to_db(u, filename, session=None):
     bin_sources = get_sources_from_name(entry["source package"], entry["source version"], session=session)
     if len(bin_sources) != 1:
         raise NoSourceFieldError, "Unable to find a unique source id for %s (%s), %s, file %s, type %s, signed by %s" % \
-                                  (bin.package, bin.version, bin.architecture.arch_string,
+                                  (bin.package, bin.version, entry["architecture"],
                                    filename, bin.binarytype, u.pkg.changes["fingerprint"])
 
     bin.source_id = bin_sources[0].source_id
@@ -2453,15 +2477,14 @@ SUITE_FIELDS = [ ('SuiteName', 'suite_name'),
                  ('Priority', 'priority'),
                  ('NotAutomatic', 'notautomatic'),
                  ('CopyChanges', 'copychanges'),
-                 ('CopyDotDak', 'copydotdak'),
-                 ('CommentsDir', 'commentsdir'),
-                 ('OverrideSuite', 'overridesuite'),
-                 ('ChangelogBase', 'changelogbase')]
-
+                 ('OverrideSuite', 'overridesuite')]
 
+# Why the heck don't we have any UNIQUE constraints in table suite?
+# TODO: Add UNIQUE constraints for appropriate columns.
 class Suite(object):
-    def __init__(self, *args, **kwargs):
-        pass
+    def __init__(self, suite_name = None, version = None):
+        self.suite_name = suite_name
+        self.version = version
 
     def __repr__(self):
         return '<Suite %s>' % self.suite_name
@@ -2487,38 +2510,49 @@ class Suite(object):
 
         return "\n".join(ret)
 
-__all__.append('Suite')
+    def get_architectures(self, skipsrc=False, skipall=False):
+        """
+        Returns list of Architecture objects
 
-@session_wrapper
-def get_suite_architecture(suite, architecture, session=None):
-    """
-    Returns a SuiteArchitecture object given C{suite} and ${arch} or None if it
-    doesn't exist
+        @type skipsrc: boolean
+        @param skipsrc: Whether to skip returning the 'source' architecture entry
+        (Default False)
 
-    @type suite: str
-    @param suite: Suite name to search for
+        @type skipall: boolean
+        @param skipall: Whether to skip returning the 'all' architecture entry
+        (Default False)
 
-    @type architecture: str
-    @param architecture: Architecture name to search for
+        @rtype: list
+        @return: list of Architecture objects for the given name (may be empty)
+        """
 
-    @type session: Session
-    @param session: Optional SQL session object (a temporary one will be
-    generated if not supplied)
+        q = object_session(self).query(Architecture). \
+            filter(Architecture.suites.contains(self))
+        if skipsrc:
+            q = q.filter(Architecture.arch_string != 'source')
+        if skipall:
+            q = q.filter(Architecture.arch_string != 'all')
+        return q.order_by(Architecture.arch_string).all()
 
-    @rtype: SuiteArchitecture
-    @return: the SuiteArchitecture object or None
-    """
+    def get_sources(self, source):
+        """
+        Returns a query object representing DBSource that is part of C{suite}.
 
-    q = session.query(SuiteArchitecture)
-    q = q.join(Architecture).filter_by(arch_string=architecture)
-    q = q.join(Suite).filter_by(suite_name=suite)
+          - B{source} - source package name, eg. I{mailfilter}, I{bbdb}, I{glibc}
 
-    try:
-        return q.one()
-    except NoResultFound:
-        return None
+        @type source: string
+        @param source: source package name
 
-__all__.append('get_suite_architecture')
+        @rtype: sqlalchemy.orm.query.Query
+        @return: a query of DBSource
+
+        """
+
+        session = object_session(self)
+        return session.query(DBSource).filter_by(source = source). \
+            filter(DBSource.suites.contains(self))
+
+__all__.append('Suite')
 
 @session_wrapper
 def get_suite(suite, session=None):
@@ -2547,15 +2581,7 @@ __all__.append('get_suite')
 
 ################################################################################
 
-class SuiteArchitecture(object):
-    def __init__(self, *args, **kwargs):
-        pass
-
-    def __repr__(self):
-        return '<SuiteArchitecture (%s, %s)>' % (self.suite_id, self.arch_id)
-
-__all__.append('SuiteArchitecture')
-
+# TODO: should be removed because the implementation is too trivial
 @session_wrapper
 def get_suite_architectures(suite, skipsrc=False, skipall=False, session=None):
     """
@@ -2580,19 +2606,7 @@ def get_suite_architectures(suite, skipsrc=False, skipall=False, session=None):
     @return: list of Architecture objects for the given name (may be empty)
     """
 
-    q = session.query(Architecture)
-    q = q.join(SuiteArchitecture)
-    q = q.join(Suite).filter_by(suite_name=suite)
-
-    if skipsrc:
-        q = q.filter(Architecture.arch_string != 'source')
-
-    if skipall:
-        q = q.filter(Architecture.arch_string != 'all')
-
-    q = q.order_by('arch_string')
-
-    return q.all()
+    return get_suite(suite, session).get_architectures(skipsrc, skipall)
 
 __all__.append('get_suite_architectures')
 
@@ -2635,8 +2649,9 @@ __all__.append('get_suite_src_formats')
 ################################################################################
 
 class Uid(object):
-    def __init__(self, *args, **kwargs):
-        pass
+    def __init__(self, uid = None, name = None):
+        self.uid = uid
+        self.name = name
 
     def __eq__(self, val):
         if isinstance(val, str):
@@ -2655,28 +2670,6 @@ class Uid(object):
 
 __all__.append('Uid')
 
-@session_wrapper
-def add_database_user(uidname, session=None):
-    """
-    Adds a database user
-
-    @type uidname: string
-    @param uidname: The uid of the user to add
-
-    @type session: SQLAlchemy
-    @param session: Optional SQL session object (a temporary one will be
-    generated if not supplied).  If not passed, a commit will be performed at
-    the end of the function, otherwise the caller is responsible for commiting.
-
-    @rtype: Uid
-    @return: the uid object for the given uidname
-    """
-
-    session.execute("CREATE USER :uid", {'uid': uidname})
-    session.commit_or_flush()
-
-__all__.append('add_database_user')
-
 @session_wrapper
 def get_or_set_uid(uidname, session=None):
     """
@@ -2751,35 +2744,28 @@ class DBConn(object):
             self.__createconn()
 
     def __setuptables(self):
-        tables = (
+        tables_with_primary = (
             'architecture',
             'archive',
             'bin_associations',
             'binaries',
             'binary_acl',
             'binary_acl_map',
-            'bin_contents',
             'build_queue',
-            'build_queue_files',
+            'changelogs_text',
             'component',
             'config',
             'changes_pending_binaries',
             'changes_pending_files',
-            'changes_pending_files_map',
             'changes_pending_source',
-            'changes_pending_source_files',
-            'changes_pool_files',
-            'deb_contents',
             'dsc_files',
             'files',
             'fingerprint',
             'keyrings',
-            'changes',
             'keyring_acl_map',
             'location',
             'maintainer',
             'new_comments',
-            'override',
             'override_type',
             'pending_bin_contents',
             'policy_queue',
@@ -2791,21 +2777,78 @@ class DBConn(object):
             'src_format',
             'src_uploaders',
             'suite',
+            'uid',
+            'upload_blocks',
+            # The following tables have primary keys but sqlalchemy
+            # version 0.5 fails to reflect them correctly with database
+            # versions before upgrade #41.
+            #'changes',
+            #'build_queue_files',
+        )
+
+        tables_no_primary = (
+            'bin_contents',
+            'changes_pending_files_map',
+            'changes_pending_source_files',
+            'changes_pool_files',
+            'deb_contents',
+            'override',
             'suite_architectures',
             'suite_src_formats',
             'suite_build_queue_copy',
             'udeb_contents',
-            'uid',
-            'upload_blocks',
+            # see the comment above
+            'changes',
+            'build_queue_files',
         )
 
-        for table_name in tables:
+        views = (
+            'almost_obsolete_all_associations',
+            'almost_obsolete_src_associations',
+            'any_associations_source',
+            'bin_assoc_by_arch',
+            'bin_associations_binaries',
+            'binaries_suite_arch',
+            'binfiles_suite_component_arch',
+            'changelogs',
+            'file_arch_suite',
+            'newest_all_associations',
+            'newest_any_associations',
+            'newest_source',
+            'newest_src_association',
+            'obsolete_all_associations',
+            'obsolete_any_associations',
+            'obsolete_any_by_all_associations',
+            'obsolete_src_associations',
+            'source_suite',
+            'src_associations_bin',
+            'src_associations_src',
+            'suite_arch_by_name',
+        )
+
+        # Sqlalchemy version 0.5 fails to reflect the SERIAL type
+        # correctly and that is why we have to use a workaround. It can
+        # be removed as soon as we switch to version 0.6.
+        for table_name in tables_with_primary:
+            table = Table(table_name, self.db_meta, \
+                Column('id', Integer, primary_key = True), \
+                autoload=True, useexisting=True)
+            setattr(self, 'tbl_%s' % table_name, table)
+
+        for table_name in tables_no_primary:
             table = Table(table_name, self.db_meta, autoload=True)
             setattr(self, 'tbl_%s' % table_name, table)
 
+        for view_name in views:
+            view = Table(view_name, self.db_meta, autoload=True)
+            setattr(self, 'view_%s' % view_name, view)
+
     def __setupmappers(self):
         mapper(Architecture, self.tbl_architecture,
-               properties = dict(arch_id = self.tbl_architecture.c.id))
+           properties = dict(arch_id = self.tbl_architecture.c.id,
+               suites = relation(Suite, secondary=self.tbl_suite_architectures,
+                   order_by='suite_name',
+                   backref=backref('architectures', order_by='arch_string'))))
 
         mapper(Archive, self.tbl_archive,
                properties = dict(archive_id = self.tbl_archive.c.id,
@@ -2894,7 +2937,11 @@ class DBConn(object):
                properties = dict(file_id = self.tbl_files.c.id,
                                  filesize = self.tbl_files.c.size,
                                  location_id = self.tbl_files.c.location,
-                                 location = relation(Location)))
+                                 location = relation(Location,
+                                     # using lazy='dynamic' in the back
+                                     # reference because we have A LOT of
+                                     # files in one location
+                                     backref=backref('files', lazy='dynamic'))))
 
         mapper(Fingerprint, self.tbl_fingerprint,
                properties = dict(fingerprint_id = self.tbl_fingerprint.c.id,
@@ -2967,10 +3014,16 @@ class DBConn(object):
                                  component = relation(Component),
                                  archive_id = self.tbl_location.c.archive,
                                  archive = relation(Archive),
+                                 # FIXME: the 'type' column is old cruft and
+                                 # should be removed in the future.
                                  archive_type = self.tbl_location.c.type))
 
         mapper(Maintainer, self.tbl_maintainer,
-               properties = dict(maintainer_id = self.tbl_maintainer.c.id))
+               properties = dict(maintainer_id = self.tbl_maintainer.c.id,
+                   maintains_sources = relation(DBSource, backref='maintainer',
+                       primaryjoin=(self.tbl_maintainer.c.id==self.tbl_source.c.maintainer)),
+                   changed_sources = relation(DBSource, backref='changedby',
+                       primaryjoin=(self.tbl_maintainer.c.id==self.tbl_source.c.changedby))))
 
         mapper(NewComment, self.tbl_new_comments,
                properties = dict(comment_id = self.tbl_new_comments.c.id))
@@ -3006,19 +3059,15 @@ class DBConn(object):
                properties = dict(source_id = self.tbl_source.c.id,
                                  version = self.tbl_source.c.version,
                                  maintainer_id = self.tbl_source.c.maintainer,
-                                 maintainer = relation(Maintainer,
-                                                       primaryjoin=(self.tbl_source.c.maintainer==self.tbl_maintainer.c.id)),
                                  poolfile_id = self.tbl_source.c.file,
-                                 poolfile = relation(PoolFile),
+                                 poolfile = relation(PoolFile, backref=backref('source', uselist = False)),
                                  fingerprint_id = self.tbl_source.c.sig_fpr,
                                  fingerprint = relation(Fingerprint),
                                  changedby_id = self.tbl_source.c.changedby,
-                                 changedby = relation(Maintainer,
-                                                      primaryjoin=(self.tbl_source.c.changedby==self.tbl_maintainer.c.id)),
                                  srcfiles = relation(DSCFile,
                                                      primaryjoin=(self.tbl_source.c.id==self.tbl_dsc_files.c.source)),
-                                 srcassociations = relation(SrcAssociation,
-                                                            primaryjoin=(self.tbl_source.c.id==self.tbl_src_associations.c.source)),
+                                 suites = relation(Suite, secondary=self.tbl_src_associations,
+                                     backref='sources'),
                                  srcuploaders = relation(SrcUploader)))
 
         mapper(SourceACL, self.tbl_source_acl,
@@ -3049,12 +3098,6 @@ class DBConn(object):
                                  policy_queue = relation(PolicyQueue),
                                  copy_queues = relation(BuildQueue, secondary=self.tbl_suite_build_queue_copy)))
 
-        mapper(SuiteArchitecture, self.tbl_suite_architectures,
-               properties = dict(suite_id = self.tbl_suite_architectures.c.suite,
-                                 suite = relation(Suite, backref='suitearchitectures'),
-                                 arch_id = self.tbl_suite_architectures.c.architecture,
-                                 architecture = relation(Architecture)))
-
         mapper(SuiteSrcFormat, self.tbl_suite_src_formats,
                properties = dict(suite_id = self.tbl_suite_src_formats.c.suite,
                                  suite = relation(Suite, backref='suitesrcformats'),