daklib/dbconn.py

   1 #!/usr/bin/python
   2
   3 """ DB access class
   4
   5 @contact: Debian FTPMaster <ftpmaster@debian.org>
   6 @copyright: 2000, 2001, 2002, 2003, 2004, 2006  James Troup <james@nocrew.org>
   7 @copyright: 2008-2009  Mark Hymers <mhy@debian.org>
   8 @copyright: 2009  Joerg Jaspert <joerg@debian.org>
   9 @copyright: 2009  Mike O'Connor <stew@debian.org>
  10 @license: GNU General Public License version 2 or later
  11 """
  12
  13 # This program is free software; you can redistribute it and/or modify
  14 # it under the terms of the GNU General Public License as published by
  15 # the Free Software Foundation; either version 2 of the License, or
  16 # (at your option) any later version.
  17
  18 # This program is distributed in the hope that it will be useful,
  19 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  20 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  21 # GNU General Public License for more details.
  22
  23 # You should have received a copy of the GNU General Public License
  24 # along with this program; if not, write to the Free Software
  25 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  26
  27 ################################################################################
  28
  29 # < mhy> I need a funny comment
  30 # < sgran> two peanuts were walking down a dark street
  31 # < sgran> one was a-salted
  32 #  * mhy looks up the definition of "funny"
  33
  34 ################################################################################
  35
  36 import os
  37 import psycopg2
  38 import traceback
  39
  40 from sqlalchemy import create_engine, Table, MetaData, select
  41 from sqlalchemy.orm import sessionmaker, mapper, relation
  42
  43 from singleton import Singleton
  44
  45 ################################################################################
  46
  47 class Cache(object):
  48     def __init__(self, hashfunc=None):
  49         if hashfunc:
  50             self.hashfunc = hashfunc
  51         else:
  52             self.hashfunc = lambda x: str(x)
  53
  54         self.data = {}
  55
  56     def SetValue(self, keys, value):
  57         self.data[self.hashfunc(keys)] = value
  58
  59     def GetValue(self, keys):
  60         return self.data.get(self.hashfunc(keys))
  61
  62 ################################################################################
  63
  64 class Architecture(object):
  65     def __init__(self, *args, **kwargs):
  66         pass
  67
  68     def __repr__(self):
  69         return '<Architecture %s>' % self.arch_string
  70
  71 class Archive(object):
  72     def __init__(self, *args, **kwargs):
  73         pass
  74
  75     def __repr__(self):
  76         return '<Archive %s>' % self.name
  77
  78 class BinAssociation(object):
  79     def __init__(self, *args, **kwargs):
  80         pass
  81
  82     def __repr__(self):
  83         return '<BinAssociation %s (%s, %s)>' % (self.ba_id, self.binary, self.suite)
  84
  85 class Binary(object):
  86     def __init__(self, *args, **kwargs):
  87         pass
  88
  89     def __repr__(self):
  90         return '<Binary %s (%s, %s)>' % (self.package, self.version, self.architecture)
  91
  92 def binary_from_id(id):
  93     return DBConn().session().query(Binary).filter_by(binary_id=id).one()
  94
  95 class Component(object):
  96     def __init__(self, *args, **kwargs):
  97         pass
  98
  99     def __repr__(self):
 100         return '<Component %s>' % self.component_name
 101
 102 class DBConfig(object):
 103     def __init__(self, *args, **kwargs):
 104         pass
 105
 106     def __repr__(self):
 107         return '<DBConfig %s>' % self.name
 108
 109 class ContentFilename(object):
 110     def __init__(self, *args, **kwargs):
 111         pass
 112
 113     def __repr__(self):
 114         return '<ContentFilename %s>' % self.filename
 115
 116 class ContentFilepath(object):
 117     def __init__(self, *args, **kwargs):
 118         pass
 119
 120     def __repr__(self):
 121         return '<ContentFilepath %s>' % self.filepath
 122
 123 class ContentAssociations(object):
 124     def __init__(self, *args, **kwargs):
 125         pass
 126
 127     def __repr__(self):
 128         return '<ContentAssociation %s>' % self.ca_id
 129
 130 class DSCFile(object):
 131     def __init__(self, *args, **kwargs):
 132         pass
 133
 134     def __repr__(self):
 135         return '<DSCFile %s>' % self.dscfile_id
 136
 137 class PoolFile(object):
 138     def __init__(self, *args, **kwargs):
 139         pass
 140
 141     def __repr__(self):
 142         return '<PoolFile %s>' % self.filename
 143
 144 class Fingerprint(object):
 145     def __init__(self, *args, **kwargs):
 146         pass
 147
 148     def __repr__(self):
 149         return '<Fingerprint %s>' % self.fingerprint
 150
 151 class Keyring(object):
 152     def __init__(self, *args, **kwargs):
 153         pass
 154
 155     def __repr__(self):
 156         return '<Keyring %s>' % self.keyring_name
 157
 158 class Location(object):
 159     def __init__(self, *args, **kwargs):
 160         pass
 161
 162     def __repr__(self):
 163         return '<Location %s (%s)>' % (self.path, self.location_id)
 164
 165 class Maintainer(object):
 166     def __init__(self, *args, **kwargs):
 167         pass
 168
 169     def __repr__(self):
 170         return '''<Maintainer '%s' (%s)>''' % (self.name, self.maintainer_id)
 171
 172 class Override(object):
 173     def __init__(self, *args, **kwargs):
 174         pass
 175
 176     def __repr__(self):
 177         return '<Override %s (%s)>' % (self.package, self.suite_id)
 178
 179 class OverrideType(object):
 180     def __init__(self, *args, **kwargs):
 181         pass
 182
 183     def __repr__(self):
 184         return '<OverrideType %s>' % self.overridetype
 185
 186 class PendingContentAssociation(object):
 187     def __init__(self, *args, **kwargs):
 188         pass
 189
 190     def __repr__(self):
 191         return '<PendingContentAssociation %s>' % self.pca_id
 192
 193 class Priority(object):
 194     def __init__(self, *args, **kwargs):
 195         pass
 196
 197     def __repr__(self):
 198         return '<Priority %s (%s)>' % (self.priority, self.priority_id)
 199
 200 class Queue(object):
 201     def __init__(self, *args, **kwargs):
 202         pass
 203
 204     def __repr__(self):
 205         return '<Queue %s>' % self.queue_name
 206
 207 class QueueBuild(object):
 208     def __init__(self, *args, **kwargs):
 209         pass
 210
 211     def __repr__(self):
 212         return '<QueueBuild %s (%s)>' % (self.filename, self.queue_id)
 213
 214 class Section(object):
 215     def __init__(self, *args, **kwargs):
 216         pass
 217
 218     def __repr__(self):
 219         return '<Section %s>' % self.section
 220
 221 class Source(object):
 222     def __init__(self, *args, **kwargs):
 223         pass
 224
 225     def __repr__(self):
 226         return '<Source %s (%s)>' % (self.source, self.version)
 227
 228 class SrcAssociation(object):
 229     def __init__(self, *args, **kwargs):
 230         pass
 231
 232     def __repr__(self):
 233         return '<SrcAssociation %s (%s, %s)>' % (self.sa_id, self.source, self.suite)
 234
 235 class SrcUploader(object):
 236     def __init__(self, *args, **kwargs):
 237         pass
 238
 239     def __repr__(self):
 240         return '<SrcUploader %s>' % self.uploader_id
 241
 242 class Suite(object):
 243     def __init__(self, *args, **kwargs):
 244         pass
 245
 246     def __repr__(self):
 247         return '<Suite %s>' % self.suite_name
 248
 249 class SuiteArchitecture(object):
 250     def __init__(self, *args, **kwargs):
 251         pass
 252
 253     def __repr__(self):
 254         return '<SuiteArchitecture (%s, %s)>' % (self.suite_id, self.arch_id)
 255
 256 class Uid(object):
 257     def __init__(self, *args, **kwargs):
 258         pass
 259
 260     def __repr__(self):
 261         return '<Uid %s (%s)>' % (self.uid, self.name)
 262
 263 ################################################################################
 264
 265 class DBConn(Singleton):
 266     """
 267     database module init.
 268     """
 269     def __init__(self, *args, **kwargs):
 270         super(DBConn, self).__init__(*args, **kwargs)
 271
 272     def _startup(self, *args, **kwargs):
 273         self.__createconn()
 274         self.__init_caches()
 275
 276     def __setuptables(self):
 277         self.tbl_architecture = Table('architecture', self.db_meta, autoload=True)
 278         self.tbl_archive = Table('archive', self.db_meta, autoload=True)
 279         self.tbl_bin_associations = Table('bin_associations', self.db_meta, autoload=True)
 280         self.tbl_binaries = Table('binaries', self.db_meta, autoload=True)
 281         self.tbl_component = Table('component', self.db_meta, autoload=True)
 282         self.tbl_config = Table('config', self.db_meta, autoload=True)
 283         self.tbl_content_associations = Table('content_associations', self.db_meta, autoload=True)
 284         self.tbl_content_file_names = Table('content_file_names', self.db_meta, autoload=True)
 285         self.tbl_content_file_paths = Table('content_file_paths', self.db_meta, autoload=True)
 286         self.tbl_dsc_files = Table('dsc_files', self.db_meta, autoload=True)
 287         self.tbl_files = Table('files', self.db_meta, autoload=True)
 288         self.tbl_fingerprint = Table('fingerprint', self.db_meta, autoload=True)
 289         self.tbl_keyrings = Table('keyrings', self.db_meta, autoload=True)
 290         self.tbl_location = Table('location', self.db_meta, autoload=True)
 291         self.tbl_maintainer = Table('maintainer', self.db_meta, autoload=True)
 292         self.tbl_override = Table('override', self.db_meta, autoload=True)
 293         self.tbl_override_type = Table('override_type', self.db_meta, autoload=True)
 294         self.tbl_pending_content_associations = Table('pending_content_associations', self.db_meta, autoload=True)
 295         self.tbl_priority = Table('priority', self.db_meta, autoload=True)
 296         self.tbl_queue = Table('queue', self.db_meta, autoload=True)
 297         self.tbl_queue_build = Table('queue_build', self.db_meta, autoload=True)
 298         self.tbl_section = Table('section', self.db_meta, autoload=True)
 299         self.tbl_source = Table('source', self.db_meta, autoload=True)
 300         self.tbl_src_associations = Table('src_associations', self.db_meta, autoload=True)
 301         self.tbl_src_uploaders = Table('src_uploaders', self.db_meta, autoload=True)
 302         self.tbl_suite = Table('suite', self.db_meta, autoload=True)
 303         self.tbl_suite_architectures = Table('suite_architectures', self.db_meta, autoload=True)
 304         self.tbl_uid = Table('uid', self.db_meta, autoload=True)
 305
 306     def __setupmappers(self):
 307         mapper(Architecture, self.tbl_architecture,
 308                properties = dict(arch_id = self.tbl_architecture.c.id))
 309
 310         mapper(Archive, self.tbl_archive,
 311                properties = dict(archive_id = self.tbl_archive.c.id,
 312                                  archive_name = self.tbl_archive.c.name))
 313
 314         mapper(BinAssociation, self.tbl_bin_associations,
 315                properties = dict(ba_id = self.tbl_bin_associations.c.id,
 316                                  suite_id = self.tbl_bin_associations.c.suite,
 317                                  suite = relation(Suite),
 318                                  binary_id = self.tbl_bin_associations.c.bin,
 319                                  binary = relation(Binary)))
 320
 321         mapper(Binary, self.tbl_binaries,
 322                properties = dict(binary_id = self.tbl_binaries.c.id,
 323                                  package = self.tbl_binaries.c.package,
 324                                  version = self.tbl_binaries.c.version,
 325                                  maintainer_id = self.tbl_binaries.c.maintainer,
 326                                  maintainer = relation(Maintainer),
 327                                  source_id = self.tbl_binaries.c.source,
 328                                  source = relation(Source),
 329                                  arch_id = self.tbl_binaries.c.architecture,
 330                                  architecture = relation(Architecture),
 331                                  poolfile_id = self.tbl_binaries.c.file,
 332                                  poolfile = relation(PoolFile),
 333                                  binarytype = self.tbl_binaries.c.type,
 334                                  fingerprint_id = self.tbl_binaries.c.sig_fpr,
 335                                  fingerprint = relation(Fingerprint),
 336                                  install_date = self.tbl_binaries.c.install_date,
 337                                  binassociations = relation(BinAssociation,
 338                                                             primaryjoin=(self.tbl_binaries.c.id==self.tbl_bin_associations.c.bin))))
 339
 340         mapper(Component, self.tbl_component,
 341                properties = dict(component_id = self.tbl_component.c.id,
 342                                  component_name = self.tbl_component.c.name))
 343
 344         mapper(DBConfig, self.tbl_config,
 345                properties = dict(config_id = self.tbl_config.c.id))
 346
 347         mapper(ContentAssociations, self.tbl_content_associations,
 348                properties = dict(ca_id = self.tbl_content_associations.c.id,
 349                                  filename_id = self.tbl_content_associations.c.filename,
 350                                  filename    = relation(ContentFilename),
 351                                  filepath_id = self.tbl_content_associations.c.filepath,
 352                                  filepath    = relation(ContentFilepath),
 353                                  binary_id   = self.tbl_content_associations.c.binary_pkg,
 354                                  binary      = relation(Binary)))
 355
 356
 357         mapper(ContentFilename, self.tbl_content_file_names,
 358                properties = dict(cafilename_id = self.tbl_content_file_names.c.id,
 359                                  filename = self.tbl_content_file_names.c.file))
 360
 361         mapper(ContentFilepath, self.tbl_content_file_paths,
 362                properties = dict(cafilepath_id = self.tbl_content_file_paths.c.id,
 363                                  filepath = self.tbl_content_file_paths.c.path))
 364
 365         mapper(DSCFile, self.tbl_dsc_files,
 366                properties = dict(dscfile_id = self.tbl_dsc_files.c.id,
 367                                  source_id = self.tbl_dsc_files.c.source,
 368                                  source = relation(Source),
 369                                  poolfile_id = self.tbl_dsc_files.c.file,
 370                                  poolfile = relation(PoolFile)))
 371
 372         mapper(PoolFile, self.tbl_files,
 373                properties = dict(file_id = self.tbl_files.c.id,
 374                                  filesize = self.tbl_files.c.size,
 375                                  location_id = self.tbl_files.c.location,
 376                                  location = relation(Location)))
 377
 378         mapper(Fingerprint, self.tbl_fingerprint,
 379                properties = dict(fingerprint_id = self.tbl_fingerprint.c.id,
 380                                  uid_id = self.tbl_fingerprint.c.uid,
 381                                  uid = relation(Uid),
 382                                  keyring_id = self.tbl_fingerprint.c.keyring,
 383                                  keyring = relation(Keyring)))
 384
 385         mapper(Keyring, self.tbl_keyrings,
 386                properties = dict(keyring_name = self.tbl_keyrings.c.name,
 387                                  keyring_id = self.tbl_keyrings.c.id))
 388
 389         mapper(Location, self.tbl_location,
 390                properties = dict(location_id = self.tbl_location.c.id,
 391                                  component_id = self.tbl_location.c.component,
 392                                  component = relation(Component),
 393                                  archive_id = self.tbl_location.c.archive,
 394                                  archive = relation(Archive),
 395                                  archive_type = self.tbl_location.c.type))
 396
 397         mapper(Maintainer, self.tbl_maintainer,
 398                properties = dict(maintainer_id = self.tbl_maintainer.c.id))
 399
 400         mapper(Override, self.tbl_override,
 401                properties = dict(suite_id = self.tbl_override.c.suite,
 402                                  suite = relation(Suite),
 403                                  component_id = self.tbl_override.c.component,
 404                                  component = relation(Component),
 405                                  priority_id = self.tbl_override.c.priority,
 406                                  priority = relation(Priority),
 407                                  section_id = self.tbl_override.c.section,
 408                                  section = relation(Section),
 409                                  overridetype_id = self.tbl_override.c.type,
 410                                  overridetype = relation(OverrideType)))
 411
 412         mapper(OverrideType, self.tbl_override_type,
 413                properties = dict(overridetype = self.tbl_override_type.c.type,
 414                                  overridetype_id = self.tbl_override_type.c.id))
 415
 416         mapper(PendingContentAssociation, self.tbl_pending_content_associations,
 417                properties = dict(pca_id = self.tbl_pending_content_associations.c.id,
 418                                  filepath_id = self.tbl_pending_content_associations.c.filepath,
 419                                  filepath = relation(ContentFilepath),
 420                                  filename_id = self.tbl_pending_content_associations.c.filename,
 421                                  filename = relation(ContentFilename)))
 422
 423         mapper(Priority, self.tbl_priority,
 424                properties = dict(priority_id = self.tbl_priority.c.id))
 425
 426         mapper(Queue, self.tbl_queue,
 427                properties = dict(queue_id = self.tbl_queue.c.id))
 428
 429         mapper(QueueBuild, self.tbl_queue_build,
 430                properties = dict(suite_id = self.tbl_queue_build.c.suite,
 431                                  queue_id = self.tbl_queue_build.c.queue,
 432                                  queue = relation(Queue)))
 433
 434         mapper(Section, self.tbl_section,
 435                properties = dict(section_id = self.tbl_section.c.id))
 436
 437         mapper(Source, self.tbl_source,
 438                properties = dict(source_id = self.tbl_source.c.id,
 439                                  version = self.tbl_source.c.version,
 440                                  maintainer_id = self.tbl_source.c.maintainer,
 441                                  maintainer = relation(Maintainer,
 442                                                        primaryjoin=(self.tbl_source.c.maintainer==self.tbl_maintainer.c.id)),
 443                                  poolfile_id = self.tbl_source.c.file,
 444                                  poolfile = relation(PoolFile),
 445                                  fingerprint_id = self.tbl_source.c.sig_fpr,
 446                                  fingerprint = relation(Fingerprint),
 447                                  changedby_id = self.tbl_source.c.changedby,
 448                                  changedby = relation(Maintainer,
 449                                                       primaryjoin=(self.tbl_source.c.changedby==self.tbl_maintainer.c.id)),
 450                                  srcfiles = relation(DSCFile,
 451                                                      primaryjoin=(self.tbl_source.c.id==self.tbl_dsc_files.c.source)),
 452                                  srcassociations = relation(SrcAssociation,
 453                                                             primaryjoin=(self.tbl_source.c.id==self.tbl_src_associations.c.source))))
 454
 455         mapper(SrcAssociation, self.tbl_src_associations,
 456                properties = dict(sa_id = self.tbl_src_associations.c.id,
 457                                  suite_id = self.tbl_src_associations.c.suite,
 458                                  suite = relation(Suite),
 459                                  source_id = self.tbl_src_associations.c.source,
 460                                  source = relation(Source)))
 461
 462         mapper(SrcUploader, self.tbl_src_uploaders,
 463                properties = dict(uploader_id = self.tbl_src_uploaders.c.id,
 464                                  source_id = self.tbl_src_uploaders.c.source,
 465                                  source = relation(Source,
 466                                                    primaryjoin=(self.tbl_src_uploaders.c.source==self.tbl_source.c.id)),
 467                                  maintainer_id = self.tbl_src_uploaders.c.maintainer,
 468                                  maintainer = relation(Maintainer,
 469                                                        primaryjoin=(self.tbl_src_uploaders.c.maintainer==self.tbl_maintainer.c.id))))
 470
 471         mapper(Suite, self.tbl_suite,
 472                properties = dict(suite_id = self.tbl_suite.c.id))
 473
 474         mapper(SuiteArchitecture, self.tbl_suite_architectures,
 475                properties = dict(suite_id = self.tbl_suite_architectures.c.suite,
 476                                  suite = relation(Suite),
 477                                  arch_id = self.tbl_suite_architectures.c.architecture,
 478                                  architecture = relation(Architecture)))
 479
 480         mapper(Uid, self.tbl_uid,
 481                properties = dict(uid_id = self.tbl_uid.c.id))
 482
 483     ## Connection functions
 484     def __createconn(self):
 485         from config import Config
 486         cnf = Config()
 487         if cnf["DB::Host"]:
 488             # TCP/IP
 489             connstr = "postgres://%s" % cnf["DB::Host"]
 490             if cnf["DB::Port"] and cnf["DB::Port"] != "-1":
 491                 connstr += ":%s" % cnf["DB::Port"]
 492             connstr += "/%s" % cnf["DB::Name"]
 493         else:
 494             # Unix Socket
 495             connstr = "postgres:///%s" % cnf["DB::Name"]
 496             if cnf["DB::Port"] and cnf["DB::Port"] != "-1":
 497                 connstr += "?port=%s" % cnf["DB::Port"]
 498
 499         self.db_pg   = create_engine(connstr)
 500         self.db_meta = MetaData()
 501         self.db_meta.bind = self.db_pg
 502         self.db_smaker = sessionmaker(bind=self.db_pg,
 503                                       autoflush=True,
 504                                       transactional=True)
 505
 506         self.__setuptables()
 507         self.__setupmappers()
 508
 509     def session(self):
 510         return self.db_smaker()
 511
 512     ## Cache functions
 513     def __init_caches(self):
 514         self.caches = {'suite':         Cache(),
 515                        'section':       Cache(),
 516                        'priority':      Cache(),
 517                        'override_type': Cache(),
 518                        'architecture':  Cache(),
 519                        'archive':       Cache(),
 520                        'component':     Cache(),
 521                        'content_path_names':     Cache(),
 522                        'content_file_names':     Cache(),
 523                        'location':      Cache(lambda x: '%s_%s_%s' % (x['location'], x['component'], x['location'])),
 524                        'maintainer':    {}, # TODO
 525                        'keyring':       {}, # TODO
 526                        'source':        Cache(lambda x: '%s_%s_' % (x['source'], x['version'])),
 527                        'files':         Cache(lambda x: '%s_%s_' % (x['filename'], x['location'])),
 528                        'maintainer':    {}, # TODO
 529                        'fingerprint':   {}, # TODO
 530                        'queue':         {}, # TODO
 531                        'uid':           {}, # TODO
 532                        'suite_version': Cache(lambda x: '%s_%s' % (x['source'], x['suite'])),
 533                       }
 534
 535         self.prepared_statements = {}
 536
 537     def prepare(self,name,statement):
 538         if not self.prepared_statements.has_key(name):
 539             pgc.execute(statement)
 540             self.prepared_statements[name] = statement
 541
 542     def clear_caches(self):
 543         self.__init_caches()
 544
 545     ## Get functions
 546     def __get_id(self, retfield, selectobj, cachekey, cachename=None):
 547         # This is a bit of a hack but it's an internal function only
 548         if cachename is not None:
 549             res = self.caches[cachename].GetValue(cachekey)
 550             if res:
 551                 return res
 552
 553         c = selectobj.execute()
 554
 555         if c.rowcount != 1:
 556             return None
 557
 558         res = c.fetchone()
 559
 560         if retfield not in res.keys():
 561             return None
 562
 563         res = res[retfield]
 564
 565         if cachename is not None:
 566             self.caches[cachename].SetValue(cachekey, res)
 567
 568         return res
 569
 570     def get_suite_id(self, suite):
 571         """
 572         Returns database id for given C{suite}.
 573         Results are kept in a cache during runtime to minimize database queries.
 574
 575         @type suite: string
 576         @param suite: The name of the suite
 577
 578         @rtype: int
 579         @return: the database id for the given suite
 580
 581         """
 582         return int(self.__get_id('id',
 583                                  self.tbl_suite.select(self.tbl_suite.columns.suite_name == suite),
 584                                  suite,
 585                                  'suite'))
 586
 587     def get_section_id(self, section):
 588         """
 589         Returns database id for given C{section}.
 590         Results are kept in a cache during runtime to minimize database queries.
 591
 592         @type section: string
 593         @param section: The name of the section
 594
 595         @rtype: int
 596         @return: the database id for the given section
 597
 598         """
 599         return self.__get_id('id',
 600                              self.tbl_section.select(self.tbl_section.columns.section == section),
 601                              section,
 602                              'section')
 603
 604     def get_priority_id(self, priority):
 605         """
 606         Returns database id for given C{priority}.
 607         Results are kept in a cache during runtime to minimize database queries.
 608
 609         @type priority: string
 610         @param priority: The name of the priority
 611
 612         @rtype: int
 613         @return: the database id for the given priority
 614
 615         """
 616         return self.__get_id('id',
 617                              self.tbl_priority.select(self.tbl_priority.columns.priority == priority),
 618                              priority,
 619                              'priority')
 620
 621     def get_override_type_id(self, override_type):
 622         """
 623         Returns database id for given override C{type}.
 624         Results are kept in a cache during runtime to minimize database queries.
 625
 626         @type override_type: string
 627         @param override_type: The name of the override type
 628
 629         @rtype: int
 630         @return: the database id for the given override type
 631
 632         """
 633         return self.__get_id('id',
 634                              self.tbl_override_type.select(self.tbl_override_type.columns.type == override_type),
 635                              override_type,
 636                              'override_type')
 637
 638     def get_architecture_id(self, architecture):
 639         """
 640         Returns database id for given C{architecture}.
 641         Results are kept in a cache during runtime to minimize database queries.
 642
 643         @type architecture: string
 644         @param architecture: The name of the override type
 645
 646         @rtype: int
 647         @return: the database id for the given architecture
 648
 649         """
 650         return self.__get_id('id',
 651                              self.tbl_architecture.select(self.tbl_architecture.columns.arch_string == architecture),
 652                              architecture,
 653                              'architecture')
 654
 655     def get_archive_id(self, archive):
 656         """
 657         returns database id for given c{archive}.
 658         results are kept in a cache during runtime to minimize database queries.
 659
 660         @type archive: string
 661         @param archive: the name of the override type
 662
 663         @rtype: int
 664         @return: the database id for the given archive
 665
 666         """
 667         archive = archive.lower()
 668         return self.__get_id('id',
 669                              self.tbl_archive.select(self.tbl_archive.columns.name == archive),
 670                              archive,
 671                              'archive')
 672
 673     def get_component_id(self, component):
 674         """
 675         Returns database id for given C{component}.
 676         Results are kept in a cache during runtime to minimize database queries.
 677
 678         @type component: string
 679         @param component: The name of the override type
 680
 681         @rtype: int
 682         @return: the database id for the given component
 683
 684         """
 685         component = component.lower()
 686         return self.__get_id('id',
 687                              self.tbl_component.select(self.tbl_component.columns.name == component),
 688                              component.lower(),
 689                              'component')
 690
 691     def get_location_id(self, location, component, archive):
 692         """
 693         Returns database id for the location behind the given combination of
 694           - B{location} - the path of the location, eg. I{/srv/ftp.debian.org/ftp/pool/}
 695           - B{component} - the id of the component as returned by L{get_component_id}
 696           - B{archive} - the id of the archive as returned by L{get_archive_id}
 697         Results are kept in a cache during runtime to minimize database queries.
 698
 699         @type location: string
 700         @param location: the path of the location
 701
 702         @type component: string
 703         @param component: the name of the component
 704
 705         @type archive: string
 706         @param archive: the name of the archive
 707
 708         @rtype: int
 709         @return: the database id for the location
 710
 711         """
 712
 713         archive = archive.lower()
 714         component = component.lower()
 715
 716         values = {'archive': archive, 'location': location, 'component': component}
 717
 718         s = self.tbl_location.join(self.tbl_archive).join(self.tbl_component)
 719
 720         s = s.select(self.tbl_location.columns.path == location)
 721         s = s.where(self.tbl_archive.columns.name == archive)
 722         s = s.where(self.tbl_component.columns.name == component)
 723
 724         return self.__get_id('location.id', s, values, 'location')
 725
 726     def get_source_id(self, source, version):
 727         """
 728         Returns database id for the combination of C{source} and C{version}
 729           - B{source} - source package name, eg. I{mailfilter}, I{bbdb}, I{glibc}
 730           - B{version}
 731         Results are kept in a cache during runtime to minimize database queries.
 732
 733         @type source: string
 734         @param source: source package name
 735
 736         @type version: string
 737         @param version: the source version
 738
 739         @rtype: int
 740         @return: the database id for the source
 741
 742         """
 743         s = self.tbl_source.select()
 744         s = s.where(self.tbl_source.columns.source  == source)
 745         s = s.where(self.tbl_source.columns.version == version)
 746
 747         return self.__get_id('id', s, {'source': source, 'version': version}, 'source')
 748
 749     def get_suite(self, suite):
 750         if isinstance(suite, str):
 751             suite_id = self.get_suite_id(suite.lower())
 752         elif type(suite) == int:
 753             suite_id = suite
 754
 755         s = self.tbl_suite.select(self.tbl_suite.columns.id == suite_id)
 756         c = s.execute()
 757         if c.rowcount < 1:
 758             return None
 759         else:
 760             return c.fetchone()
 761
 762     def get_suite_version(self, source, suite):
 763         """
 764         Returns database id for a combination of C{source} and C{suite}.
 765
 766           - B{source} - source package name, eg. I{mailfilter}, I{bbdb}, I{glibc}
 767           - B{suite} - a suite name, eg. I{unstable}
 768
 769         Results are kept in a cache during runtime to minimize database queries.
 770
 771         @type source: string
 772         @param source: source package name
 773
 774         @type suite: string
 775         @param suite: the suite name
 776
 777         @rtype: string
 778         @return: the version for I{source} in I{suite}
 779
 780         """
 781         s = select([self.tbl_source.columns.source, self.tbl_source.columns.version])
 782 #        s = self.tbl_source.join(self.tbl_src_associations).join(self.tbl_suite)
 783
 784         s = s.select(self.tbl_suite.columns.suite_name == suite, use_labels=True)
 785         s = s.select(self.tbl_source.columns.source == source)
 786
 787         return self.__get_id('source.version', s, {'suite': suite, 'source': source}, 'suite_version')
 788
 789
 790     def get_files_id (self, filename, size, md5sum, location_id):
 791         """
 792         Returns -1, -2 or the file_id for filename, if its C{size} and C{md5sum} match an
 793         existing copy.
 794
 795         The database is queried using the C{filename} and C{location_id}. If a file does exist
 796         at that location, the existing size and md5sum are checked against the provided
 797         parameters. A size or checksum mismatch returns -2. If more than one entry is
 798         found within the database, a -1 is returned, no result returns None, otherwise
 799         the file id.
 800
 801         Results are kept in a cache during runtime to minimize database queries.
 802
 803         @type filename: string
 804         @param filename: the filename of the file to check against the DB
 805
 806         @type size: int
 807         @param size: the size of the file to check against the DB
 808
 809         @type md5sum: string
 810         @param md5sum: the md5sum of the file to check against the DB
 811
 812         @type location_id: int
 813         @param location_id: the id of the location as returned by L{get_location_id}
 814
 815         @rtype: int / None
 816         @return: Various return values are possible:
 817                    - -2: size/checksum error
 818                    - -1: more than one file found in database
 819                    - None: no file found in database
 820                    - int: file id
 821
 822         """
 823         values = {'filename' : filename,
 824                   'location' : location_id}
 825
 826         res = self.caches['files'].GetValue( values )
 827
 828         if not res:
 829             query = """SELECT id, size, md5sum
 830                        FROM files
 831                        WHERE filename = %(filename)s AND location = %(location)s"""
 832
 833             cursor = self.db_con.cursor()
 834             cursor.execute( query, values )
 835
 836             if cursor.rowcount == 0:
 837                 res = None
 838
 839             elif cursor.rowcount != 1:
 840                 res = -1
 841
 842             else:
 843                 row = cursor.fetchone()
 844
 845                 if row[1] != int(size) or row[2] != md5sum:
 846                     res =  -2
 847
 848                 else:
 849                     self.caches['files'].SetValue(values, row[0])
 850                     res = row[0]
 851
 852         return res
 853
 854
 855     def get_or_set_contents_file_id(self, filename):
 856         """
 857         Returns database id for given filename.
 858
 859         Results are kept in a cache during runtime to minimize database queries.
 860         If no matching file is found, a row is inserted.
 861
 862         @type filename: string
 863         @param filename: The filename
 864
 865         @rtype: int
 866         @return: the database id for the given component
 867         """
 868         try:
 869             values={'value': filename}
 870             query = "SELECT id FROM content_file_names WHERE file = %(value)s"
 871             id = self.__get_single_id(query, values, cachename='content_file_names')
 872             if not id:
 873                 c = self.db_con.cursor()
 874                 c.execute( "INSERT INTO content_file_names VALUES (DEFAULT, %(value)s) RETURNING id",
 875                            values )
 876
 877                 id = c.fetchone()[0]
 878                 self.caches['content_file_names'].SetValue(values, id)
 879
 880             return id
 881         except:
 882             traceback.print_exc()
 883             raise
 884
 885     def get_or_set_contents_path_id(self, path):
 886         """
 887         Returns database id for given path.
 888
 889         Results are kept in a cache during runtime to minimize database queries.
 890         If no matching file is found, a row is inserted.
 891
 892         @type path: string
 893         @param path: The filename
 894
 895         @rtype: int
 896         @return: the database id for the given component
 897         """
 898         try:
 899             values={'value': path}
 900             query = "SELECT id FROM content_file_paths WHERE path = %(value)s"
 901             id = self.__get_single_id(query, values, cachename='content_path_names')
 902             if not id:
 903                 c = self.db_con.cursor()
 904                 c.execute( "INSERT INTO content_file_paths VALUES (DEFAULT, %(value)s) RETURNING id",
 905                            values )
 906
 907                 id = c.fetchone()[0]
 908                 self.caches['content_path_names'].SetValue(values, id)
 909
 910             return id
 911         except:
 912             traceback.print_exc()
 913             raise
 914
 915     def get_suite_architectures(self, suite):
 916         """
 917         Returns list of architectures for C{suite}.
 918
 919         @type suite: string, int
 920         @param suite: the suite name or the suite_id
 921
 922         @rtype: list
 923         @return: the list of architectures for I{suite}
 924         """
 925
 926         suite_id = None
 927         if type(suite) == str:
 928             suite_id = self.get_suite_id(suite)
 929         elif type(suite) == int:
 930             suite_id = suite
 931         else:
 932             return None
 933
 934         c = self.db_con.cursor()
 935         c.execute( """SELECT a.arch_string FROM suite_architectures sa
 936                       JOIN architecture a ON (a.id = sa.architecture)
 937                       WHERE suite='%s'""" % suite_id )
 938
 939         return map(lambda x: x[0], c.fetchall())
 940
 941     def insert_content_paths(self, bin_id, fullpaths):
 942         """
 943         Make sure given path is associated with given binary id
 944
 945         @type bin_id: int
 946         @param bin_id: the id of the binary
 947         @type fullpaths: list
 948         @param fullpaths: the list of paths of the file being associated with the binary
 949
 950         @return: True upon success
 951         """
 952
 953         c = self.db_con.cursor()
 954
 955         c.execute("BEGIN WORK")
 956         try:
 957
 958             for fullpath in fullpaths:
 959                 (path, file) = os.path.split(fullpath)
 960
 961                 if path.startswith( "./" ):
 962                     path = path[2:]
 963                 # Get the necessary IDs ...
 964                 file_id = self.get_or_set_contents_file_id(file)
 965                 path_id = self.get_or_set_contents_path_id(path)
 966
 967                 c.execute("""INSERT INTO content_associations
 968                                (binary_pkg, filepath, filename)
 969                            VALUES ( '%d', '%d', '%d')""" % (bin_id, path_id, file_id) )
 970
 971             c.execute("COMMIT")
 972             return True
 973         except:
 974             traceback.print_exc()
 975             c.execute("ROLLBACK")
 976             return False
 977
 978     def insert_pending_content_paths(self, package, fullpaths):
 979         """
 980         Make sure given paths are temporarily associated with given
 981         package
 982
 983         @type package: dict
 984         @param package: the package to associate with should have been read in from the binary control file
 985         @type fullpaths: list
 986         @param fullpaths: the list of paths of the file being associated with the binary
 987
 988         @return: True upon success
 989         """
 990
 991         c = self.db_con.cursor()
 992
 993         c.execute("BEGIN WORK")
 994         try:
 995             arch_id = self.get_architecture_id(package['Architecture'])
 996
 997             # Remove any already existing recorded files for this package
 998             c.execute("""DELETE FROM pending_content_associations
 999                          WHERE package=%(Package)s
1000                          AND version=%(Version)s
1001                          AND architecture=%(ArchID)s""", {'Package': package['Package'],
1002                                                           'Version': package['Version'],
1003                                                           'ArchID':  arch_id})
1004
1005             for fullpath in fullpaths:
1006                 (path, file) = os.path.split(fullpath)
1007
1008                 if path.startswith( "./" ):
1009                     path = path[2:]
1010                 # Get the necessary IDs ...
1011                 file_id = self.get_or_set_contents_file_id(file)
1012                 path_id = self.get_or_set_contents_path_id(path)
1013
1014                 c.execute("""INSERT INTO pending_content_associations
1015                                (package, version, architecture, filepath, filename)
1016                             VALUES (%%(Package)s, %%(Version)s, '%d', '%d', '%d')"""
1017                     % (arch_id, path_id, file_id), package )
1018
1019             c.execute("COMMIT")
1020             return True
1021         except:
1022             traceback.print_exc()
1023             c.execute("ROLLBACK")
1024             return False
1025