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