3 """ DB access functions
4 @group readonly: get_suite_id, get_section_id, get_priority_id, get_override_type_id,
5 get_architecture_id, get_archive_id, get_component_id, get_location_id,
6 get_source_id, get_suite_version, get_files_id, get_maintainer, get_suites,
7 get_suite_architectures
8 @group read/write: get_or_set*, set_files_id
10 @contact: Debian FTP Master <ftpmaster@debian.org>
11 @copyright: 2000, 2001, 2002, 2003, 2004, 2006 James Troup <james@nocrew.org>
12 @copyright: 2009 Joerg Jaspert <joerg@debian.org>
13 @license: GNU General Public License version 2 or later
16 # This program is free software; you can redistribute it and/or modify
17 # it under the terms of the GNU General Public License as published by
18 # the Free Software Foundation; either version 2 of the License, or
19 # (at your option) any later version.
21 # This program is distributed in the hope that it will be useful,
22 # but WITHOUT ANY WARRANTY; without even the implied warranty of
23 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
24 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
30 ################################################################################
36 from binary import Binary
38 ################################################################################
40 Cnf = None #: Configuration, apt_pkg.Configuration
41 projectB = None #: database connection, pgobject
42 suite_id_cache = {} #: cache for suites
43 section_id_cache = {} #: cache for sections
44 priority_id_cache = {} #: cache for priorities
45 override_type_id_cache = {} #: cache for overrides
46 architecture_id_cache = {} #: cache for architectures
47 archive_id_cache = {} #: cache for archives
48 component_id_cache = {} #: cache for components
49 location_id_cache = {} #: cache for locations
50 maintainer_id_cache = {} #: cache for maintainers
51 keyring_id_cache = {} #: cache for keyrings
52 source_id_cache = {} #: cache for sources
54 files_id_cache = {} #: cache for files
55 maintainer_cache = {} #: cache for maintainer names
56 fingerprint_id_cache = {} #: cache for fingerprints
57 queue_id_cache = {} #: cache for queues
58 uid_id_cache = {} #: cache for uids
59 suite_version_cache = {} #: cache for suite_versions (packages)
60 suite_bin_version_cache = {}
61 cache_preloaded = False
63 ################################################################################
65 def init (config, sql):
69 @type config: apt_pkg.Configuration
70 @param config: apt config, see U{http://apt.alioth.debian.org/python-apt-doc/apt_pkg/cache.html#Configuration}
73 @param sql: database connection
84 Executes a database query. Writes statistics / timing to stderr.
87 @param query: database query string, passed unmodified
91 @warning: The query is passed B{unmodified}, so be careful what you use this for.
93 sys.stderr.write("query: \"%s\" ... " % (query))
95 r = projectB.query(query)
96 time_diff = time.time()-before
97 sys.stderr.write("took %.3f seconds.\n" % (time_diff))
99 sys.stderr.write("int result: %s\n" % (r))
100 elif type(r) is types.NoneType:
101 sys.stderr.write("result: None\n")
103 sys.stderr.write("pgresult: %s\n" % (r.getresult()))
106 ################################################################################
108 def get_suite_id (suite):
110 Returns database id for given C{suite}.
111 Results are kept in a cache during runtime to minimize database queries.
114 @param suite: The name of the suite
117 @return: the database id for the given suite
120 global suite_id_cache
122 if suite_id_cache.has_key(suite):
123 return suite_id_cache[suite]
125 q = projectB.query("SELECT id FROM suite WHERE suite_name = '%s'" % (suite))
131 suite_id_cache[suite] = suite_id
135 def get_section_id (section):
137 Returns database id for given C{section}.
138 Results are kept in a cache during runtime to minimize database queries.
140 @type section: string
141 @param section: The name of the section
144 @return: the database id for the given section
147 global section_id_cache
149 if section_id_cache.has_key(section):
150 return section_id_cache[section]
152 q = projectB.query("SELECT id FROM section WHERE section = '%s'" % (section))
157 section_id = ql[0][0]
158 section_id_cache[section] = section_id
162 def get_priority_id (priority):
164 Returns database id for given C{priority}.
165 Results are kept in a cache during runtime to minimize database queries.
167 @type priority: string
168 @param priority: The name of the priority
171 @return: the database id for the given priority
174 global priority_id_cache
176 if priority_id_cache.has_key(priority):
177 return priority_id_cache[priority]
179 q = projectB.query("SELECT id FROM priority WHERE priority = '%s'" % (priority))
184 priority_id = ql[0][0]
185 priority_id_cache[priority] = priority_id
189 def get_override_type_id (type):
191 Returns database id for given override C{type}.
192 Results are kept in a cache during runtime to minimize database queries.
195 @param type: The name of the override type
198 @return: the database id for the given override type
201 global override_type_id_cache
203 if override_type_id_cache.has_key(type):
204 return override_type_id_cache[type]
206 q = projectB.query("SELECT id FROM override_type WHERE type = '%s'" % (type))
211 override_type_id = ql[0][0]
212 override_type_id_cache[type] = override_type_id
214 return override_type_id
216 def get_architecture_id (architecture):
218 Returns database id for given C{architecture}.
219 Results are kept in a cache during runtime to minimize database queries.
221 @type architecture: string
222 @param architecture: The name of the override type
225 @return: the database id for the given architecture
228 global architecture_id_cache
230 if architecture_id_cache.has_key(architecture):
231 return architecture_id_cache[architecture]
233 q = projectB.query("SELECT id FROM architecture WHERE arch_string = '%s'" % (architecture))
238 architecture_id = ql[0][0]
239 architecture_id_cache[architecture] = architecture_id
241 return architecture_id
243 def get_archive_id (archive):
245 Returns database id for given C{archive}.
246 Results are kept in a cache during runtime to minimize database queries.
248 @type archive: string
249 @param archive: The name of the override type
252 @return: the database id for the given archive
255 global archive_id_cache
257 archive = archive.lower()
259 if archive_id_cache.has_key(archive):
260 return archive_id_cache[archive]
262 q = projectB.query("SELECT id FROM archive WHERE lower(name) = '%s'" % (archive))
267 archive_id = ql[0][0]
268 archive_id_cache[archive] = archive_id
272 def get_component_id (component):
274 Returns database id for given C{component}.
275 Results are kept in a cache during runtime to minimize database queries.
277 @type component: string
278 @param component: The name of the component
281 @return: the database id for the given component
284 global component_id_cache
286 component = component.lower()
288 if component_id_cache.has_key(component):
289 return component_id_cache[component]
291 q = projectB.query("SELECT id FROM component WHERE lower(name) = '%s'" % (component))
296 component_id = ql[0][0]
297 component_id_cache[component] = component_id
301 def get_location_id (location, component, archive):
303 Returns database id for the location behind the given combination of
304 - B{location} - the path of the location, eg. I{/srv/ftp.debian.org/ftp/pool/}
305 - B{component} - the id of the component as returned by L{get_component_id}
306 - B{archive} - the id of the archive as returned by L{get_archive_id}
307 Results are kept in a cache during runtime to minimize database queries.
309 @type location: string
310 @param location: the path of the location
313 @param component: the id of the component
316 @param archive: the id of the archive
319 @return: the database id for the location
322 global location_id_cache
324 cache_key = location + '_' + component + '_' + location
325 if location_id_cache.has_key(cache_key):
326 return location_id_cache[cache_key]
328 archive_id = get_archive_id (archive)
330 component_id = get_component_id (component)
331 if component_id != -1:
332 q = projectB.query("SELECT id FROM location WHERE path = '%s' AND component = %d AND archive = %d" % (location, component_id, archive_id))
334 q = projectB.query("SELECT id FROM location WHERE path = '%s' AND archive = %d" % (location, archive_id))
339 location_id = ql[0][0]
340 location_id_cache[cache_key] = location_id
344 def get_source_id (source, version):
346 Returns database id for the combination of C{source} and C{version}
347 - B{source} - source package name, eg. I{mailfilter}, I{bbdb}, I{glibc}
349 Results are kept in a cache during runtime to minimize database queries.
352 @param source: source package name
354 @type version: string
355 @param version: the source version
358 @return: the database id for the source
361 global source_id_cache
363 cache_key = source + '_' + version + '_'
364 if source_id_cache.has_key(cache_key):
365 return source_id_cache[cache_key]
367 q = projectB.query("SELECT id FROM source s WHERE s.source = '%s' AND s.version = '%s'" % (source, version))
369 if not q.getresult():
372 source_id = q.getresult()[0][0]
373 source_id_cache[cache_key] = source_id
377 def get_suite_version(source, suite):
379 Returns database id for a combination of C{source} and C{suite}.
381 - B{source} - source package name, eg. I{mailfilter}, I{bbdb}, I{glibc}
382 - B{suite} - a suite name, eg. I{unstable}
384 Results are kept in a cache during runtime to minimize database queries.
387 @param source: source package name
390 @param suite: the suite name
393 @return: the version for I{source} in I{suite}
397 global suite_version_cache
398 cache_key = "%s_%s" % (source, suite)
400 if suite_version_cache.has_key(cache_key):
401 return suite_version_cache[cache_key]
403 q = projectB.query("""
404 SELECT s.version FROM source s, suite su, src_associations sa
407 AND su.suite_name='%s'
411 if not q.getresult():
414 version = q.getresult()[0][0]
415 suite_version_cache[cache_key] = version
419 def get_latest_binary_version_id(binary, section, suite, arch):
420 global suite_bin_version_cache
421 cache_key = "%s_%s_%s_%s" % (binary, section, suite, arch)
422 cache_key_all = "%s_%s_%s_%s" % (binary, section, suite, get_architecture_id("all"))
424 # Check for the cache hit for its arch, then arch all
425 if suite_bin_version_cache.has_key(cache_key):
426 return suite_bin_version_cache[cache_key]
427 if suite_bin_version_cache.has_key(cache_key_all):
428 return suite_bin_version_cache[cache_key_all]
429 if cache_preloaded == True:
430 return # package does not exist
432 q = projectB.query("SELECT DISTINCT b.id FROM binaries b JOIN bin_associations ba ON (b.id = ba.bin) JOIN override o ON (o.package=b.package) WHERE b.package = '%s' AND b.architecture = '%d' AND ba.suite = '%d' AND o.section = '%d'" % (binary, int(arch), int(suite), int(section)))
434 if not q.getresult():
437 highest_bid = q.getresult()[0][0]
439 suite_bin_version_cache[cache_key] = highest_bid
442 def preload_binary_id_cache():
443 global suite_bin_version_cache, cache_preloaded
446 q = projectB.query("SELECT id FROM suite")
447 suites = q.getresult()
450 q = projectB.query("SELECT id FROM architecture")
451 arches = q.getresult()
455 q = projectB.query("SELECT DISTINCT b.id, b.package, o.section FROM binaries b JOIN bin_associations ba ON (b.id = ba.bin) JOIN override o ON (o.package=b.package) WHERE b.architecture = '%d' AND ba.suite = '%d'" % (int(arch[0]), int(suite[0])))
457 for bi in q.getresult():
458 cache_key = "%s_%s_%s_%s" % (bi[1], bi[2], suite[0], arch[0])
459 suite_bin_version_cache[cache_key] = int(bi[0])
461 cache_preloaded = True
463 def get_suite_architectures(suite):
465 Returns list of architectures for C{suite}.
467 @type suite: string, int
468 @param suite: the suite name or the suite_id
471 @return: the list of architectures for I{suite}
475 if type(suite) == str:
476 suite_id = get_suite_id(suite)
477 elif type(suite) == int:
482 sql = """ SELECT a.arch_string FROM suite_architectures sa
483 JOIN architecture a ON (a.id = sa.architecture)
484 WHERE suite='%s' """ % (suite_id)
486 q = projectB.query(sql)
487 return map(lambda x: x[0], q.getresult())
489 def get_suite_untouchable(suite):
491 Returns true if the C{suite} is untouchable, otherwise false.
493 @type suite: string, int
494 @param suite: the suite name or the suite_id
497 @return: status of suite
501 if type(suite) == str:
502 suite_id = get_suite_id(suite.lower())
503 elif type(suite) == int:
508 sql = """ SELECT untouchable FROM suite WHERE id='%s' """ % (suite_id)
510 q = projectB.query(sql)
511 if q.getresult()[0][0] == "f":
516 ################################################################################
518 def get_or_set_maintainer_id (maintainer):
520 If C{maintainer} does not have an entry in the maintainer table yet, create one
521 and return the new id.
522 If C{maintainer} already has an entry, simply return the existing id.
524 Results are kept in a cache during runtime to minimize database queries.
526 @type maintainer: string
527 @param maintainer: the maintainer name
530 @return: the database id for the maintainer
533 global maintainer_id_cache
535 if maintainer_id_cache.has_key(maintainer):
536 return maintainer_id_cache[maintainer]
538 q = projectB.query("SELECT id FROM maintainer WHERE name = '%s'" % (maintainer))
539 if not q.getresult():
540 projectB.query("INSERT INTO maintainer (name) VALUES ('%s')" % (maintainer))
541 q = projectB.query("SELECT id FROM maintainer WHERE name = '%s'" % (maintainer))
542 maintainer_id = q.getresult()[0][0]
543 maintainer_id_cache[maintainer] = maintainer_id
547 ################################################################################
549 def get_or_set_keyring_id (keyring):
551 If C{keyring} does not have an entry in the C{keyrings} table yet, create one
552 and return the new id.
553 If C{keyring} already has an entry, simply return the existing id.
555 Results are kept in a cache during runtime to minimize database queries.
557 @type keyring: string
558 @param keyring: the keyring name
561 @return: the database id for the keyring
564 global keyring_id_cache
566 if keyring_id_cache.has_key(keyring):
567 return keyring_id_cache[keyring]
569 q = projectB.query("SELECT id FROM keyrings WHERE name = '%s'" % (keyring))
570 if not q.getresult():
571 projectB.query("INSERT INTO keyrings (name) VALUES ('%s')" % (keyring))
572 q = projectB.query("SELECT id FROM keyrings WHERE name = '%s'" % (keyring))
573 keyring_id = q.getresult()[0][0]
574 keyring_id_cache[keyring] = keyring_id
578 ################################################################################
580 def get_or_set_uid_id (uid):
582 If C{uid} does not have an entry in the uid table yet, create one
583 and return the new id.
584 If C{uid} already has an entry, simply return the existing id.
586 Results are kept in a cache during runtime to minimize database queries.
592 @return: the database id for the uid
598 if uid_id_cache.has_key(uid):
599 return uid_id_cache[uid]
601 q = projectB.query("SELECT id FROM uid WHERE uid = '%s'" % (uid))
602 if not q.getresult():
603 projectB.query("INSERT INTO uid (uid) VALUES ('%s')" % (uid))
604 q = projectB.query("SELECT id FROM uid WHERE uid = '%s'" % (uid))
605 uid_id = q.getresult()[0][0]
606 uid_id_cache[uid] = uid_id
610 ################################################################################
612 def get_or_set_fingerprint_id (fingerprint):
614 If C{fingerprint} does not have an entry in the fingerprint table yet, create one
615 and return the new id.
616 If C{fingerprint} already has an entry, simply return the existing id.
618 Results are kept in a cache during runtime to minimize database queries.
620 @type fingerprint: string
621 @param fingerprint: the fingerprint
624 @return: the database id for the fingerprint
627 global fingerprint_id_cache
629 if fingerprint_id_cache.has_key(fingerprint):
630 return fingerprint_id_cache[fingerprint]
632 q = projectB.query("SELECT id FROM fingerprint WHERE fingerprint = '%s'" % (fingerprint))
633 if not q.getresult():
634 projectB.query("INSERT INTO fingerprint (fingerprint) VALUES ('%s')" % (fingerprint))
635 q = projectB.query("SELECT id FROM fingerprint WHERE fingerprint = '%s'" % (fingerprint))
636 fingerprint_id = q.getresult()[0][0]
637 fingerprint_id_cache[fingerprint] = fingerprint_id
639 return fingerprint_id
641 ################################################################################
643 def get_files_id (filename, size, md5sum, location_id):
645 Returns -1, -2 or the file_id for filename, if its C{size} and C{md5sum} match an
648 The database is queried using the C{filename} and C{location_id}. If a file does exist
649 at that location, the existing size and md5sum are checked against the provided
650 parameters. A size or checksum mismatch returns -2. If more than one entry is
651 found within the database, a -1 is returned, no result returns None, otherwise
654 Results are kept in a cache during runtime to minimize database queries.
656 @type filename: string
657 @param filename: the filename of the file to check against the DB
660 @param size: the size of the file to check against the DB
663 @param md5sum: the md5sum of the file to check against the DB
665 @type location_id: int
666 @param location_id: the id of the location as returned by L{get_location_id}
669 @return: Various return values are possible:
670 - -2: size/checksum error
671 - -1: more than one file found in database
672 - None: no file found in database
676 global files_id_cache
678 cache_key = "%s_%d" % (filename, location_id)
680 if files_id_cache.has_key(cache_key):
681 return files_id_cache[cache_key]
684 q = projectB.query("SELECT id, size, md5sum FROM files WHERE filename = '%s' AND location = %d" % (filename, location_id))
690 orig_size = int(ql[1])
692 if orig_size != size or orig_md5sum != md5sum:
694 files_id_cache[cache_key] = ql[0]
695 return files_id_cache[cache_key]
699 ################################################################################
701 def get_or_set_queue_id (queue):
703 If C{queue} does not have an entry in the queue table yet, create one
704 and return the new id.
705 If C{queue} already has an entry, simply return the existing id.
707 Results are kept in a cache during runtime to minimize database queries.
710 @param queue: the queue name (no full path)
713 @return: the database id for the queue
716 global queue_id_cache
718 if queue_id_cache.has_key(queue):
719 return queue_id_cache[queue]
721 q = projectB.query("SELECT id FROM queue WHERE queue_name = '%s'" % (queue))
722 if not q.getresult():
723 projectB.query("INSERT INTO queue (queue_name) VALUES ('%s')" % (queue))
724 q = projectB.query("SELECT id FROM queue WHERE queue_name = '%s'" % (queue))
725 queue_id = q.getresult()[0][0]
726 queue_id_cache[queue] = queue_id
730 ################################################################################
732 def set_files_id (filename, size, md5sum, sha1sum, sha256sum, location_id):
734 Insert a new entry into the files table and return its id.
736 @type filename: string
737 @param filename: the filename
740 @param size: the size in bytes
743 @param md5sum: md5sum of the file
745 @type sha1sum: string
746 @param sha1sum: sha1sum of the file
748 @type sha256sum: string
749 @param sha256sum: sha256sum of the file
751 @type location_id: int
752 @param location_id: the id of the location as returned by L{get_location_id}
755 @return: the database id for the new file
758 global files_id_cache
760 projectB.query("INSERT INTO files (filename, size, md5sum, sha1sum, sha256sum, location) VALUES ('%s', %d, '%s', '%s', '%s', %d)" % (filename, long(size), md5sum, sha1sum, sha256sum, location_id))
762 return get_files_id (filename, size, md5sum, location_id)
764 ### currval has issues with postgresql 7.1.3 when the table is big
765 ### it was taking ~3 seconds to return on auric which is very Not
768 ##q = projectB.query("SELECT id FROM files WHERE id = currval('files_id_seq')")
769 ##ql = q.getresult()[0]
770 ##cache_key = "%s_%d" % (filename, location_id)
771 ##files_id_cache[cache_key] = ql[0]
772 ##return files_id_cache[cache_key]
774 ################################################################################
776 def get_maintainer (maintainer_id):
778 Return the name of the maintainer behind C{maintainer_id}.
780 Results are kept in a cache during runtime to minimize database queries.
782 @type maintainer_id: int
783 @param maintainer_id: the id of the maintainer, eg. from L{get_or_set_maintainer_id}
786 @return: the name of the maintainer
789 global maintainer_cache
791 if not maintainer_cache.has_key(maintainer_id):
792 q = projectB.query("SELECT name FROM maintainer WHERE id = %s" % (maintainer_id))
793 maintainer_cache[maintainer_id] = q.getresult()[0][0]
795 return maintainer_cache[maintainer_id]
797 ################################################################################
799 def get_suites(pkgname, src=False):
801 Return the suites in which C{pkgname} can be found. If C{src} is True query for source
802 package, else binary package.
804 @type pkgname: string
805 @param pkgname: name of the package
808 @param src: if True look for source packages, false (default) looks for binary.
811 @return: list of suites, or empty list if no match
820 WHERE source.id = src_associations.source
821 AND source.source = '%s'
822 AND src_associations.suite = suite.id
830 WHERE binaries.id = bin_associations.bin
832 AND bin_associations.suite = suite.id
835 q = projectB.query(sql)
836 return map(lambda x: x[0], q.getresult())
839 ################################################################################
841 def copy_temporary_contents(package, version, arch, deb, reject):
843 copy the previously stored contents from the temp table to the permanant one
845 during process-unchecked, the deb should have been scanned and the
846 contents stored in pending_content_associations
849 # first see if contents exist:
851 arch_id = get_architecture_id (arch)
853 exists = projectB.query("""SELECT 1 FROM pending_content_associations
856 AND architecture=%d LIMIT 1"""
857 % (package, version, arch_id) ).getresult()
860 # This should NOT happen. We should have added contents
861 # during process-unchecked. if it did, log an error, and send
864 "__PACKAGE__": package,
865 "__VERSION__": version,
867 "__TO_ADDRESS__": Cnf["Dinstall::MyAdminAddress"],
868 "__DAK_ADDRESS__": Cnf["Dinstall::MyEmailAddress"] }
870 message = utils.TemplateSubst(subst, Cnf["Dir::Templates"]+"/missing-contents")
871 utils.send_mail( message )
873 exists = Binary(deb, reject).scan_package()
876 sql = """INSERT INTO content_associations(binary_pkg,filepath,filename)
877 SELECT currval('binaries_id_seq'), filepath, filename FROM pending_content_associations
880 AND architecture=%d""" % (package, version, arch_id)
882 projectB.query("""DELETE from pending_content_associations
885 AND architecture=%d""" % (package, version, arch_id))