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, get_new_comments, has_new_comment
8 @group read/write: get_or_set*, set_files_id
9 @group writeonly: add_new_comment, delete_new_comments
11 @contact: Debian FTP Master <ftpmaster@debian.org>
12 @copyright: 2000, 2001, 2002, 2003, 2004, 2006 James Troup <james@nocrew.org>
13 @copyright: 2009 Joerg Jaspert <joerg@debian.org>
14 @license: GNU General Public License version 2 or later
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
27 # You should have received a copy of the GNU General Public License
28 # along with this program; if not, write to the Free Software
29 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
31 ################################################################################
36 ################################################################################
38 Cnf = None #: Configuration, apt_pkg.Configuration
39 projectB = None #: database connection, pgobject
40 suite_id_cache = {} #: cache for suites
41 section_id_cache = {} #: cache for sections
42 priority_id_cache = {} #: cache for priorities
43 override_type_id_cache = {} #: cache for overrides
44 architecture_id_cache = {} #: cache for architectures
45 archive_id_cache = {} #: cache for archives
46 component_id_cache = {} #: cache for components
47 location_id_cache = {} #: cache for locations
48 maintainer_id_cache = {} #: cache for maintainers
49 keyring_id_cache = {} #: cache for keyrings
50 source_id_cache = {} #: cache for sources
52 files_id_cache = {} #: cache for files
53 maintainer_cache = {} #: cache for maintainer names
54 fingerprint_id_cache = {} #: cache for fingerprints
55 queue_id_cache = {} #: cache for queues
56 uid_id_cache = {} #: cache for uids
57 suite_version_cache = {} #: cache for suite_versions (packages)
58 suite_bin_version_cache = {}
59 cache_preloaded = False
61 ################################################################################
63 def init (config, sql):
67 @type config: apt_pkg.Configuration
68 @param config: apt config, see U{http://apt.alioth.debian.org/python-apt-doc/apt_pkg/cache.html#Configuration}
71 @param sql: database connection
79 ################################################################################
81 def get_suite_id (suite):
83 Returns database id for given C{suite}.
84 Results are kept in a cache during runtime to minimize database queries.
87 @param suite: The name of the suite
90 @return: the database id for the given suite
95 if suite_id_cache.has_key(suite):
96 return suite_id_cache[suite]
98 q = projectB.query("SELECT id FROM suite WHERE suite_name = '%s'" % (suite))
104 suite_id_cache[suite] = suite_id
108 def get_section_id (section):
110 Returns database id for given C{section}.
111 Results are kept in a cache during runtime to minimize database queries.
113 @type section: string
114 @param section: The name of the section
117 @return: the database id for the given section
120 global section_id_cache
122 if section_id_cache.has_key(section):
123 return section_id_cache[section]
125 q = projectB.query("SELECT id FROM section WHERE section = '%s'" % (section))
130 section_id = ql[0][0]
131 section_id_cache[section] = section_id
135 def get_priority_id (priority):
137 Returns database id for given C{priority}.
138 Results are kept in a cache during runtime to minimize database queries.
140 @type priority: string
141 @param priority: The name of the priority
144 @return: the database id for the given priority
147 global priority_id_cache
149 if priority_id_cache.has_key(priority):
150 return priority_id_cache[priority]
152 q = projectB.query("SELECT id FROM priority WHERE priority = '%s'" % (priority))
157 priority_id = ql[0][0]
158 priority_id_cache[priority] = priority_id
162 def get_override_type_id (type):
164 Returns database id for given override C{type}.
165 Results are kept in a cache during runtime to minimize database queries.
168 @param type: The name of the override type
171 @return: the database id for the given override type
174 global override_type_id_cache
176 if override_type_id_cache.has_key(type):
177 return override_type_id_cache[type]
179 q = projectB.query("SELECT id FROM override_type WHERE type = '%s'" % (type))
184 override_type_id = ql[0][0]
185 override_type_id_cache[type] = override_type_id
187 return override_type_id
189 def get_architecture_id (architecture):
191 Returns database id for given C{architecture}.
192 Results are kept in a cache during runtime to minimize database queries.
194 @type architecture: string
195 @param architecture: The name of the override type
198 @return: the database id for the given architecture
201 global architecture_id_cache
203 if architecture_id_cache.has_key(architecture):
204 return architecture_id_cache[architecture]
206 q = projectB.query("SELECT id FROM architecture WHERE arch_string = '%s'" % (architecture))
211 architecture_id = ql[0][0]
212 architecture_id_cache[architecture] = architecture_id
214 return architecture_id
216 def get_archive_id (archive):
218 Returns database id for given C{archive}.
219 Results are kept in a cache during runtime to minimize database queries.
221 @type archive: string
222 @param archive: The name of the override type
225 @return: the database id for the given archive
228 global archive_id_cache
230 archive = archive.lower()
232 if archive_id_cache.has_key(archive):
233 return archive_id_cache[archive]
235 q = projectB.query("SELECT id FROM archive WHERE lower(name) = '%s'" % (archive))
240 archive_id = ql[0][0]
241 archive_id_cache[archive] = archive_id
245 def get_component_id (component):
247 Returns database id for given C{component}.
248 Results are kept in a cache during runtime to minimize database queries.
250 @type component: string
251 @param component: The name of the component
254 @return: the database id for the given component
257 global component_id_cache
259 component = component.lower()
261 if component_id_cache.has_key(component):
262 return component_id_cache[component]
264 q = projectB.query("SELECT id FROM component WHERE lower(name) = '%s'" % (component))
269 component_id = ql[0][0]
270 component_id_cache[component] = component_id
274 def get_location_id (location, component, archive):
276 Returns database id for the location behind the given combination of
277 - B{location} - the path of the location, eg. I{/srv/ftp.debian.org/ftp/pool/}
278 - B{component} - the id of the component as returned by L{get_component_id}
279 - B{archive} - the id of the archive as returned by L{get_archive_id}
280 Results are kept in a cache during runtime to minimize database queries.
282 @type location: string
283 @param location: the path of the location
286 @param component: the id of the component
289 @param archive: the id of the archive
292 @return: the database id for the location
295 global location_id_cache
297 cache_key = location + '_' + component + '_' + location
298 if location_id_cache.has_key(cache_key):
299 return location_id_cache[cache_key]
301 archive_id = get_archive_id (archive)
303 component_id = get_component_id (component)
304 if component_id != -1:
305 q = projectB.query("SELECT id FROM location WHERE path = '%s' AND component = %d AND archive = %d" % (location, component_id, archive_id))
307 q = projectB.query("SELECT id FROM location WHERE path = '%s' AND archive = %d" % (location, archive_id))
312 location_id = ql[0][0]
313 location_id_cache[cache_key] = location_id
317 def get_source_id (source, version):
319 Returns database id for the combination of C{source} and C{version}
320 - B{source} - source package name, eg. I{mailfilter}, I{bbdb}, I{glibc}
322 Results are kept in a cache during runtime to minimize database queries.
325 @param source: source package name
327 @type version: string
328 @param version: the source version
331 @return: the database id for the source
334 global source_id_cache
336 cache_key = source + '_' + version + '_'
337 if source_id_cache.has_key(cache_key):
338 return source_id_cache[cache_key]
340 q = projectB.query("SELECT id FROM source s WHERE s.source = '%s' AND s.version = '%s'" % (source, version))
342 if not q.getresult():
345 source_id = q.getresult()[0][0]
346 source_id_cache[cache_key] = source_id
350 def get_suite_version(source, suite):
352 Returns database id for a combination of C{source} and C{suite}.
354 - B{source} - source package name, eg. I{mailfilter}, I{bbdb}, I{glibc}
355 - B{suite} - a suite name, eg. I{unstable}
357 Results are kept in a cache during runtime to minimize database queries.
360 @param source: source package name
363 @param suite: the suite name
366 @return: the version for I{source} in I{suite}
370 global suite_version_cache
371 cache_key = "%s_%s" % (source, suite)
373 if suite_version_cache.has_key(cache_key):
374 return suite_version_cache[cache_key]
376 q = projectB.query("""
377 SELECT s.version FROM source s, suite su, src_associations sa
380 AND su.suite_name='%s'
384 if not q.getresult():
387 version = q.getresult()[0][0]
388 suite_version_cache[cache_key] = version
392 def get_latest_binary_version_id(binary, section, suite, arch):
393 global suite_bin_version_cache
394 cache_key = "%s_%s_%s_%s" % (binary, section, suite, arch)
395 cache_key_all = "%s_%s_%s_%s" % (binary, section, suite, get_architecture_id("all"))
397 # Check for the cache hit for its arch, then arch all
398 if suite_bin_version_cache.has_key(cache_key):
399 return suite_bin_version_cache[cache_key]
400 if suite_bin_version_cache.has_key(cache_key_all):
401 return suite_bin_version_cache[cache_key_all]
402 if cache_preloaded == True:
403 return # package does not exist
405 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)))
407 if not q.getresult():
410 highest_bid = q.getresult()[0][0]
412 suite_bin_version_cache[cache_key] = highest_bid
415 def preload_binary_id_cache():
416 global suite_bin_version_cache, cache_preloaded
419 q = projectB.query("SELECT id FROM suite")
420 suites = q.getresult()
423 q = projectB.query("SELECT id FROM architecture")
424 arches = q.getresult()
428 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])))
430 for bi in q.getresult():
431 cache_key = "%s_%s_%s_%s" % (bi[1], bi[2], suite[0], arch[0])
432 suite_bin_version_cache[cache_key] = int(bi[0])
434 cache_preloaded = True
436 def get_suite_architectures(suite):
438 Returns list of architectures for C{suite}.
440 @type suite: string, int
441 @param suite: the suite name or the suite_id
444 @return: the list of architectures for I{suite}
448 if type(suite) == str:
449 suite_id = get_suite_id(suite)
450 elif type(suite) == int:
455 sql = """ SELECT a.arch_string FROM suite_architectures sa
456 JOIN architecture a ON (a.id = sa.architecture)
457 WHERE suite='%s' """ % (suite_id)
459 q = projectB.query(sql)
460 return map(lambda x: x[0], q.getresult())
462 def get_suite_untouchable(suite):
464 Returns true if the C{suite} is untouchable, otherwise false.
466 @type suite: string, int
467 @param suite: the suite name or the suite_id
470 @return: status of suite
474 if type(suite) == str:
475 suite_id = get_suite_id(suite.lower())
476 elif type(suite) == int:
481 sql = """ SELECT untouchable FROM suite WHERE id='%s' """ % (suite_id)
483 q = projectB.query(sql)
484 if q.getresult()[0][0] == "f":
489 ################################################################################
491 def get_or_set_maintainer_id (maintainer):
493 If C{maintainer} does not have an entry in the maintainer table yet, create one
494 and return the new id.
495 If C{maintainer} already has an entry, simply return the existing id.
497 Results are kept in a cache during runtime to minimize database queries.
499 @type maintainer: string
500 @param maintainer: the maintainer name
503 @return: the database id for the maintainer
506 global maintainer_id_cache
508 if maintainer_id_cache.has_key(maintainer):
509 return maintainer_id_cache[maintainer]
511 q = projectB.query("SELECT id FROM maintainer WHERE name = '%s'" % (maintainer))
512 if not q.getresult():
513 projectB.query("INSERT INTO maintainer (name) VALUES ('%s')" % (maintainer))
514 q = projectB.query("SELECT id FROM maintainer WHERE name = '%s'" % (maintainer))
515 maintainer_id = q.getresult()[0][0]
516 maintainer_id_cache[maintainer] = maintainer_id
520 ################################################################################
522 def get_or_set_keyring_id (keyring):
524 If C{keyring} does not have an entry in the C{keyrings} table yet, create one
525 and return the new id.
526 If C{keyring} already has an entry, simply return the existing id.
528 Results are kept in a cache during runtime to minimize database queries.
530 @type keyring: string
531 @param keyring: the keyring name
534 @return: the database id for the keyring
537 global keyring_id_cache
539 if keyring_id_cache.has_key(keyring):
540 return keyring_id_cache[keyring]
542 q = projectB.query("SELECT id FROM keyrings WHERE name = '%s'" % (keyring))
543 if not q.getresult():
544 projectB.query("INSERT INTO keyrings (name) VALUES ('%s')" % (keyring))
545 q = projectB.query("SELECT id FROM keyrings WHERE name = '%s'" % (keyring))
546 keyring_id = q.getresult()[0][0]
547 keyring_id_cache[keyring] = keyring_id
551 ################################################################################
553 def get_or_set_uid_id (uid):
555 If C{uid} does not have an entry in the uid table yet, create one
556 and return the new id.
557 If C{uid} already has an entry, simply return the existing id.
559 Results are kept in a cache during runtime to minimize database queries.
565 @return: the database id for the uid
571 if uid_id_cache.has_key(uid):
572 return uid_id_cache[uid]
574 q = projectB.query("SELECT id FROM uid WHERE uid = '%s'" % (uid))
575 if not q.getresult():
576 projectB.query("INSERT INTO uid (uid) VALUES ('%s')" % (uid))
577 q = projectB.query("SELECT id FROM uid WHERE uid = '%s'" % (uid))
578 uid_id = q.getresult()[0][0]
579 uid_id_cache[uid] = uid_id
583 ################################################################################
585 def get_or_set_fingerprint_id (fingerprint):
587 If C{fingerprint} does not have an entry in the fingerprint table yet, create one
588 and return the new id.
589 If C{fingerprint} already has an entry, simply return the existing id.
591 Results are kept in a cache during runtime to minimize database queries.
593 @type fingerprint: string
594 @param fingerprint: the fingerprint
597 @return: the database id for the fingerprint
600 global fingerprint_id_cache
602 if fingerprint_id_cache.has_key(fingerprint):
603 return fingerprint_id_cache[fingerprint]
605 q = projectB.query("SELECT id FROM fingerprint WHERE fingerprint = '%s'" % (fingerprint))
606 if not q.getresult():
607 projectB.query("INSERT INTO fingerprint (fingerprint) VALUES ('%s')" % (fingerprint))
608 q = projectB.query("SELECT id FROM fingerprint WHERE fingerprint = '%s'" % (fingerprint))
609 fingerprint_id = q.getresult()[0][0]
610 fingerprint_id_cache[fingerprint] = fingerprint_id
612 return fingerprint_id
614 ################################################################################
616 def get_files_id (filename, size, md5sum, location_id):
618 Returns -1, -2 or the file_id for filename, if its C{size} and C{md5sum} match an
621 The database is queried using the C{filename} and C{location_id}. If a file does exist
622 at that location, the existing size and md5sum are checked against the provided
623 parameters. A size or checksum mismatch returns -2. If more than one entry is
624 found within the database, a -1 is returned, no result returns None, otherwise
627 Results are kept in a cache during runtime to minimize database queries.
629 @type filename: string
630 @param filename: the filename of the file to check against the DB
633 @param size: the size of the file to check against the DB
636 @param md5sum: the md5sum of the file to check against the DB
638 @type location_id: int
639 @param location_id: the id of the location as returned by L{get_location_id}
642 @return: Various return values are possible:
643 - -2: size/checksum error
644 - -1: more than one file found in database
645 - None: no file found in database
649 global files_id_cache
651 cache_key = "%s_%d" % (filename, location_id)
653 if files_id_cache.has_key(cache_key):
654 return files_id_cache[cache_key]
657 q = projectB.query("SELECT id, size, md5sum FROM files WHERE filename = '%s' AND location = %d" % (filename, location_id))
663 orig_size = int(ql[1])
665 if orig_size != size or orig_md5sum != md5sum:
667 files_id_cache[cache_key] = ql[0]
668 return files_id_cache[cache_key]
672 ################################################################################
674 def get_or_set_queue_id (queue):
676 If C{queue} does not have an entry in the queue table yet, create one
677 and return the new id.
678 If C{queue} already has an entry, simply return the existing id.
680 Results are kept in a cache during runtime to minimize database queries.
683 @param queue: the queue name (no full path)
686 @return: the database id for the queue
689 global queue_id_cache
691 if queue_id_cache.has_key(queue):
692 return queue_id_cache[queue]
694 q = projectB.query("SELECT id FROM queue WHERE queue_name = '%s'" % (queue))
695 if not q.getresult():
696 projectB.query("INSERT INTO queue (queue_name) VALUES ('%s')" % (queue))
697 q = projectB.query("SELECT id FROM queue WHERE queue_name = '%s'" % (queue))
698 queue_id = q.getresult()[0][0]
699 queue_id_cache[queue] = queue_id
703 ################################################################################
705 def set_files_id (filename, size, md5sum, sha1sum, sha256sum, location_id):
707 Insert a new entry into the files table and return its id.
709 @type filename: string
710 @param filename: the filename
713 @param size: the size in bytes
716 @param md5sum: md5sum of the file
718 @type sha1sum: string
719 @param sha1sum: sha1sum of the file
721 @type sha256sum: string
722 @param sha256sum: sha256sum of the file
724 @type location_id: int
725 @param location_id: the id of the location as returned by L{get_location_id}
728 @return: the database id for the new file
731 global files_id_cache
733 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))
735 return get_files_id (filename, size, md5sum, location_id)
737 ### currval has issues with postgresql 7.1.3 when the table is big
738 ### it was taking ~3 seconds to return on auric which is very Not
741 ##q = projectB.query("SELECT id FROM files WHERE id = currval('files_id_seq')")
742 ##ql = q.getresult()[0]
743 ##cache_key = "%s_%d" % (filename, location_id)
744 ##files_id_cache[cache_key] = ql[0]
745 ##return files_id_cache[cache_key]
747 ################################################################################
749 def get_maintainer (maintainer_id):
751 Return the name of the maintainer behind C{maintainer_id}.
753 Results are kept in a cache during runtime to minimize database queries.
755 @type maintainer_id: int
756 @param maintainer_id: the id of the maintainer, eg. from L{get_or_set_maintainer_id}
759 @return: the name of the maintainer
762 global maintainer_cache
764 if not maintainer_cache.has_key(maintainer_id):
765 q = projectB.query("SELECT name FROM maintainer WHERE id = %s" % (maintainer_id))
766 maintainer_cache[maintainer_id] = q.getresult()[0][0]
768 return maintainer_cache[maintainer_id]
770 ################################################################################
772 def get_suites(pkgname, src=False):
774 Return the suites in which C{pkgname} can be found. If C{src} is True query for source
775 package, else binary package.
777 @type pkgname: string
778 @param pkgname: name of the package
781 @param src: if True look for source packages, false (default) looks for binary.
784 @return: list of suites, or empty list if no match
793 WHERE source.id = src_associations.source
794 AND source.source = '%s'
795 AND src_associations.suite = suite.id
803 WHERE binaries.id = bin_associations.bin
805 AND bin_associations.suite = suite.id
808 q = projectB.query(sql)
809 return map(lambda x: x[0], q.getresult())
812 ################################################################################
814 def get_new_comments(package):
816 Returns all the possible comments attached to C{package} in NEW. All versions.
818 @type package: string
819 @param package: name of the package
822 @return: list of strings containing comments for all versions from all authors for package
826 query = projectB.query(""" SELECT version, comment, author, notedate
832 for row in query.getresult():
833 comments.append("\nAuthor: %s\nVersion: %s\nTimestamp: %s\n\n%s\n" % (row[2], row[0], row[3], row[1]))
834 comments.append("-"*72)
838 def has_new_comment(package, version, ignore_trainee=False):
840 Returns true if the given combination of C{package}, C{version} has a comment.
841 If C{ignore_trainee} is true, comments from a trainee are ignored.
843 @type package: string
844 @param package: name of the package
846 @type version: string
847 @param version: package version
849 @type ignore_trainee: boolean
850 @param ignore_trainee: ignore trainee comments
858 trainee='AND trainee=false'
860 exists = projectB.query("""SELECT 1 FROM new_comments
865 % (package, version, trainee) ).getresult()
872 def add_new_comment(package, version, comment, author, trainee=False):
874 Add a new comment for C{package}, C{version} written by C{author}
876 @type package: string
877 @param package: name of the package
879 @type version: string
880 @param version: package version
882 @type comment: string
883 @param comment: the comment
886 @param author: the authorname
888 @type trainee: boolean
889 @param trainee: trainee comment
892 projectB.query(""" INSERT INTO new_comments (package, version, comment, author, trainee)
893 VALUES ('%s', '%s', '%s', '%s', '%s')
894 """ % (package, version, pg.escape_string(comment), pg.escape_string(author), trainee))
898 def delete_new_comments(package, version):
900 Delete a comment for C{package}, C{version}, if one exists
903 projectB.query(""" DELETE FROM new_comments
904 WHERE package = '%s' AND version = '%s'
905 """ % (package, version))
908 def delete_all_new_comments(package):
910 Delete all comments for C{package}, if they exist
913 projectB.query(""" DELETE FROM new_comments
918 ################################################################################
919 def copy_temporary_contents(package, version, arch, deb, reject):
921 copy the previously stored contents from the temp table to the permanant one
923 during process-unchecked, the deb should have been scanned and the
924 contents stored in pending_content_associations
927 # first see if contents exist:
929 arch_id = get_architecture_id (arch)
931 exists = projectB.query("""SELECT 1 FROM pending_content_associations
934 AND architecture=%d LIMIT 1"""
935 % (package, version, arch_id) ).getresult()
938 # This should NOT happen. We should have added contents
939 # during process-unchecked. if it did, log an error, and send
942 "__PACKAGE__": package,
943 "__VERSION__": version,
945 "__TO_ADDRESS__": Cnf["Dinstall::MyAdminAddress"],
946 "__DAK_ADDRESS__": Cnf["Dinstall::MyEmailAddress"] }
948 message = utils.TemplateSubst(subst, Cnf["Dir::Templates"]+"/missing-contents")
949 utils.send_mail( message )
951 exists = Binary(deb, reject).scan_package()
954 sql = """INSERT INTO content_associations(binary_pkg,filepath,filename)
955 SELECT currval('binaries_id_seq'), filepath, filename FROM pending_content_associations
958 AND architecture=%d""" % (package, version, arch_id)
960 projectB.query("""DELETE from pending_content_associations
963 AND architecture=%d""" % (package, version, arch_id))