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 ################################################################################
37 from binary import Binary
39 ################################################################################
41 Cnf = None #: Configuration, apt_pkg.Configuration
42 projectB = None #: database connection, pgobject
43 suite_id_cache = {} #: cache for suites
44 section_id_cache = {} #: cache for sections
45 priority_id_cache = {} #: cache for priorities
46 override_type_id_cache = {} #: cache for overrides
47 architecture_id_cache = {} #: cache for architectures
48 archive_id_cache = {} #: cache for archives
49 component_id_cache = {} #: cache for components
50 location_id_cache = {} #: cache for locations
51 maintainer_id_cache = {} #: cache for maintainers
52 keyring_id_cache = {} #: cache for keyrings
53 source_id_cache = {} #: cache for sources
55 files_id_cache = {} #: cache for files
56 maintainer_cache = {} #: cache for maintainer names
57 fingerprint_id_cache = {} #: cache for fingerprints
58 queue_id_cache = {} #: cache for queues
59 uid_id_cache = {} #: cache for uids
60 suite_version_cache = {} #: cache for suite_versions (packages)
61 suite_bin_version_cache = {}
62 cache_preloaded = False
64 ################################################################################
66 def init (config, sql):
70 @type config: apt_pkg.Configuration
71 @param config: apt config, see U{http://apt.alioth.debian.org/python-apt-doc/apt_pkg/cache.html#Configuration}
74 @param sql: database connection
85 Executes a database query. Writes statistics / timing to stderr.
88 @param query: database query string, passed unmodified
92 @warning: The query is passed B{unmodified}, so be careful what you use this for.
94 sys.stderr.write("query: \"%s\" ... " % (query))
96 r = projectB.query(query)
97 time_diff = time.time()-before
98 sys.stderr.write("took %.3f seconds.\n" % (time_diff))
100 sys.stderr.write("int result: %s\n" % (r))
101 elif type(r) is types.NoneType:
102 sys.stderr.write("result: None\n")
104 sys.stderr.write("pgresult: %s\n" % (r.getresult()))
107 ################################################################################
109 def get_suite_id (suite):
111 Returns database id for given C{suite}.
112 Results are kept in a cache during runtime to minimize database queries.
115 @param suite: The name of the suite
118 @return: the database id for the given suite
121 global suite_id_cache
123 if suite_id_cache.has_key(suite):
124 return suite_id_cache[suite]
126 q = projectB.query("SELECT id FROM suite WHERE suite_name = '%s'" % (suite))
132 suite_id_cache[suite] = suite_id
136 def get_section_id (section):
138 Returns database id for given C{section}.
139 Results are kept in a cache during runtime to minimize database queries.
141 @type section: string
142 @param section: The name of the section
145 @return: the database id for the given section
148 global section_id_cache
150 if section_id_cache.has_key(section):
151 return section_id_cache[section]
153 q = projectB.query("SELECT id FROM section WHERE section = '%s'" % (section))
158 section_id = ql[0][0]
159 section_id_cache[section] = section_id
163 def get_priority_id (priority):
165 Returns database id for given C{priority}.
166 Results are kept in a cache during runtime to minimize database queries.
168 @type priority: string
169 @param priority: The name of the priority
172 @return: the database id for the given priority
175 global priority_id_cache
177 if priority_id_cache.has_key(priority):
178 return priority_id_cache[priority]
180 q = projectB.query("SELECT id FROM priority WHERE priority = '%s'" % (priority))
185 priority_id = ql[0][0]
186 priority_id_cache[priority] = priority_id
190 def get_override_type_id (type):
192 Returns database id for given override C{type}.
193 Results are kept in a cache during runtime to minimize database queries.
196 @param type: The name of the override type
199 @return: the database id for the given override type
202 global override_type_id_cache
204 if override_type_id_cache.has_key(type):
205 return override_type_id_cache[type]
207 q = projectB.query("SELECT id FROM override_type WHERE type = '%s'" % (type))
212 override_type_id = ql[0][0]
213 override_type_id_cache[type] = override_type_id
215 return override_type_id
217 def get_architecture_id (architecture):
219 Returns database id for given C{architecture}.
220 Results are kept in a cache during runtime to minimize database queries.
222 @type architecture: string
223 @param architecture: The name of the override type
226 @return: the database id for the given architecture
229 global architecture_id_cache
231 if architecture_id_cache.has_key(architecture):
232 return architecture_id_cache[architecture]
234 q = projectB.query("SELECT id FROM architecture WHERE arch_string = '%s'" % (architecture))
239 architecture_id = ql[0][0]
240 architecture_id_cache[architecture] = architecture_id
242 return architecture_id
244 def get_archive_id (archive):
246 Returns database id for given C{archive}.
247 Results are kept in a cache during runtime to minimize database queries.
249 @type archive: string
250 @param archive: The name of the override type
253 @return: the database id for the given archive
256 global archive_id_cache
258 archive = archive.lower()
260 if archive_id_cache.has_key(archive):
261 return archive_id_cache[archive]
263 q = projectB.query("SELECT id FROM archive WHERE lower(name) = '%s'" % (archive))
268 archive_id = ql[0][0]
269 archive_id_cache[archive] = archive_id
273 def get_component_id (component):
275 Returns database id for given C{component}.
276 Results are kept in a cache during runtime to minimize database queries.
278 @type component: string
279 @param component: The name of the component
282 @return: the database id for the given component
285 global component_id_cache
287 component = component.lower()
289 if component_id_cache.has_key(component):
290 return component_id_cache[component]
292 q = projectB.query("SELECT id FROM component WHERE lower(name) = '%s'" % (component))
297 component_id = ql[0][0]
298 component_id_cache[component] = component_id
302 def get_location_id (location, component, archive):
304 Returns database id for the location behind the given combination of
305 - B{location} - the path of the location, eg. I{/srv/ftp.debian.org/ftp/pool/}
306 - B{component} - the id of the component as returned by L{get_component_id}
307 - B{archive} - the id of the archive as returned by L{get_archive_id}
308 Results are kept in a cache during runtime to minimize database queries.
310 @type location: string
311 @param location: the path of the location
314 @param component: the id of the component
317 @param archive: the id of the archive
320 @return: the database id for the location
323 global location_id_cache
325 cache_key = location + '_' + component + '_' + location
326 if location_id_cache.has_key(cache_key):
327 return location_id_cache[cache_key]
329 archive_id = get_archive_id (archive)
331 component_id = get_component_id (component)
332 if component_id != -1:
333 q = projectB.query("SELECT id FROM location WHERE path = '%s' AND component = %d AND archive = %d" % (location, component_id, archive_id))
335 q = projectB.query("SELECT id FROM location WHERE path = '%s' AND archive = %d" % (location, archive_id))
340 location_id = ql[0][0]
341 location_id_cache[cache_key] = location_id
345 def get_source_id (source, version):
347 Returns database id for the combination of C{source} and C{version}
348 - B{source} - source package name, eg. I{mailfilter}, I{bbdb}, I{glibc}
350 Results are kept in a cache during runtime to minimize database queries.
353 @param source: source package name
355 @type version: string
356 @param version: the source version
359 @return: the database id for the source
362 global source_id_cache
364 cache_key = source + '_' + version + '_'
365 if source_id_cache.has_key(cache_key):
366 return source_id_cache[cache_key]
368 q = projectB.query("SELECT id FROM source s WHERE s.source = '%s' AND s.version = '%s'" % (source, version))
370 if not q.getresult():
373 source_id = q.getresult()[0][0]
374 source_id_cache[cache_key] = source_id
378 def get_suite_version(source, suite):
380 Returns database id for a combination of C{source} and C{suite}.
382 - B{source} - source package name, eg. I{mailfilter}, I{bbdb}, I{glibc}
383 - B{suite} - a suite name, eg. I{unstable}
385 Results are kept in a cache during runtime to minimize database queries.
388 @param source: source package name
391 @param suite: the suite name
394 @return: the version for I{source} in I{suite}
398 global suite_version_cache
399 cache_key = "%s_%s" % (source, suite)
401 if suite_version_cache.has_key(cache_key):
402 return suite_version_cache[cache_key]
404 q = projectB.query("""
405 SELECT s.version FROM source s, suite su, src_associations sa
408 AND su.suite_name='%s'
412 if not q.getresult():
415 version = q.getresult()[0][0]
416 suite_version_cache[cache_key] = version
420 def get_latest_binary_version_id(binary, section, suite, arch):
421 global suite_bin_version_cache
422 cache_key = "%s_%s_%s_%s" % (binary, section, suite, arch)
423 cache_key_all = "%s_%s_%s_%s" % (binary, section, suite, get_architecture_id("all"))
425 # Check for the cache hit for its arch, then arch all
426 if suite_bin_version_cache.has_key(cache_key):
427 return suite_bin_version_cache[cache_key]
428 if suite_bin_version_cache.has_key(cache_key_all):
429 return suite_bin_version_cache[cache_key_all]
430 if cache_preloaded == True:
431 return # package does not exist
433 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)))
435 if not q.getresult():
438 highest_bid = q.getresult()[0][0]
440 suite_bin_version_cache[cache_key] = highest_bid
443 def preload_binary_id_cache():
444 global suite_bin_version_cache, cache_preloaded
447 q = projectB.query("SELECT id FROM suite")
448 suites = q.getresult()
451 q = projectB.query("SELECT id FROM architecture")
452 arches = q.getresult()
456 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])))
458 for bi in q.getresult():
459 cache_key = "%s_%s_%s_%s" % (bi[1], bi[2], suite[0], arch[0])
460 suite_bin_version_cache[cache_key] = int(bi[0])
462 cache_preloaded = True
464 def get_suite_architectures(suite):
466 Returns list of architectures for C{suite}.
468 @type suite: string, int
469 @param suite: the suite name or the suite_id
472 @return: the list of architectures for I{suite}
476 if type(suite) == str:
477 suite_id = get_suite_id(suite)
478 elif type(suite) == int:
483 sql = """ SELECT a.arch_string FROM suite_architectures sa
484 JOIN architecture a ON (a.id = sa.architecture)
485 WHERE suite='%s' """ % (suite_id)
487 q = projectB.query(sql)
488 return map(lambda x: x[0], q.getresult())
490 def get_suite_untouchable(suite):
492 Returns true if the C{suite} is untouchable, otherwise false.
494 @type suite: string, int
495 @param suite: the suite name or the suite_id
498 @return: status of suite
502 if type(suite) == str:
503 suite_id = get_suite_id(suite.lower())
504 elif type(suite) == int:
509 sql = """ SELECT untouchable FROM suite WHERE id='%s' """ % (suite_id)
511 q = projectB.query(sql)
512 if q.getresult()[0][0] == "f":
517 ################################################################################
519 def get_or_set_maintainer_id (maintainer):
521 If C{maintainer} does not have an entry in the maintainer table yet, create one
522 and return the new id.
523 If C{maintainer} already has an entry, simply return the existing id.
525 Results are kept in a cache during runtime to minimize database queries.
527 @type maintainer: string
528 @param maintainer: the maintainer name
531 @return: the database id for the maintainer
534 global maintainer_id_cache
536 if maintainer_id_cache.has_key(maintainer):
537 return maintainer_id_cache[maintainer]
539 q = projectB.query("SELECT id FROM maintainer WHERE name = '%s'" % (maintainer))
540 if not q.getresult():
541 projectB.query("INSERT INTO maintainer (name) VALUES ('%s')" % (maintainer))
542 q = projectB.query("SELECT id FROM maintainer WHERE name = '%s'" % (maintainer))
543 maintainer_id = q.getresult()[0][0]
544 maintainer_id_cache[maintainer] = maintainer_id
548 ################################################################################
550 def get_or_set_keyring_id (keyring):
552 If C{keyring} does not have an entry in the C{keyrings} table yet, create one
553 and return the new id.
554 If C{keyring} already has an entry, simply return the existing id.
556 Results are kept in a cache during runtime to minimize database queries.
558 @type keyring: string
559 @param keyring: the keyring name
562 @return: the database id for the keyring
565 global keyring_id_cache
567 if keyring_id_cache.has_key(keyring):
568 return keyring_id_cache[keyring]
570 q = projectB.query("SELECT id FROM keyrings WHERE name = '%s'" % (keyring))
571 if not q.getresult():
572 projectB.query("INSERT INTO keyrings (name) VALUES ('%s')" % (keyring))
573 q = projectB.query("SELECT id FROM keyrings WHERE name = '%s'" % (keyring))
574 keyring_id = q.getresult()[0][0]
575 keyring_id_cache[keyring] = keyring_id
579 ################################################################################
581 def get_or_set_uid_id (uid):
583 If C{uid} does not have an entry in the uid table yet, create one
584 and return the new id.
585 If C{uid} already has an entry, simply return the existing id.
587 Results are kept in a cache during runtime to minimize database queries.
593 @return: the database id for the uid
599 if uid_id_cache.has_key(uid):
600 return uid_id_cache[uid]
602 q = projectB.query("SELECT id FROM uid WHERE uid = '%s'" % (uid))
603 if not q.getresult():
604 projectB.query("INSERT INTO uid (uid) VALUES ('%s')" % (uid))
605 q = projectB.query("SELECT id FROM uid WHERE uid = '%s'" % (uid))
606 uid_id = q.getresult()[0][0]
607 uid_id_cache[uid] = uid_id
611 ################################################################################
613 def get_or_set_fingerprint_id (fingerprint):
615 If C{fingerprint} does not have an entry in the fingerprint table yet, create one
616 and return the new id.
617 If C{fingerprint} already has an entry, simply return the existing id.
619 Results are kept in a cache during runtime to minimize database queries.
621 @type fingerprint: string
622 @param fingerprint: the fingerprint
625 @return: the database id for the fingerprint
628 global fingerprint_id_cache
630 if fingerprint_id_cache.has_key(fingerprint):
631 return fingerprint_id_cache[fingerprint]
633 q = projectB.query("SELECT id FROM fingerprint WHERE fingerprint = '%s'" % (fingerprint))
634 if not q.getresult():
635 projectB.query("INSERT INTO fingerprint (fingerprint) VALUES ('%s')" % (fingerprint))
636 q = projectB.query("SELECT id FROM fingerprint WHERE fingerprint = '%s'" % (fingerprint))
637 fingerprint_id = q.getresult()[0][0]
638 fingerprint_id_cache[fingerprint] = fingerprint_id
640 return fingerprint_id
642 ################################################################################
644 def get_files_id (filename, size, md5sum, location_id):
646 Returns -1, -2 or the file_id for filename, if its C{size} and C{md5sum} match an
649 The database is queried using the C{filename} and C{location_id}. If a file does exist
650 at that location, the existing size and md5sum are checked against the provided
651 parameters. A size or checksum mismatch returns -2. If more than one entry is
652 found within the database, a -1 is returned, no result returns None, otherwise
655 Results are kept in a cache during runtime to minimize database queries.
657 @type filename: string
658 @param filename: the filename of the file to check against the DB
661 @param size: the size of the file to check against the DB
664 @param md5sum: the md5sum of the file to check against the DB
666 @type location_id: int
667 @param location_id: the id of the location as returned by L{get_location_id}
670 @return: Various return values are possible:
671 - -2: size/checksum error
672 - -1: more than one file found in database
673 - None: no file found in database
677 global files_id_cache
679 cache_key = "%s_%d" % (filename, location_id)
681 if files_id_cache.has_key(cache_key):
682 return files_id_cache[cache_key]
685 q = projectB.query("SELECT id, size, md5sum FROM files WHERE filename = '%s' AND location = %d" % (filename, location_id))
691 orig_size = int(ql[1])
693 if orig_size != size or orig_md5sum != md5sum:
695 files_id_cache[cache_key] = ql[0]
696 return files_id_cache[cache_key]
700 ################################################################################
702 def get_or_set_queue_id (queue):
704 If C{queue} does not have an entry in the queue table yet, create one
705 and return the new id.
706 If C{queue} already has an entry, simply return the existing id.
708 Results are kept in a cache during runtime to minimize database queries.
711 @param queue: the queue name (no full path)
714 @return: the database id for the queue
717 global queue_id_cache
719 if queue_id_cache.has_key(queue):
720 return queue_id_cache[queue]
722 q = projectB.query("SELECT id FROM queue WHERE queue_name = '%s'" % (queue))
723 if not q.getresult():
724 projectB.query("INSERT INTO queue (queue_name) VALUES ('%s')" % (queue))
725 q = projectB.query("SELECT id FROM queue WHERE queue_name = '%s'" % (queue))
726 queue_id = q.getresult()[0][0]
727 queue_id_cache[queue] = queue_id
731 ################################################################################
733 def set_files_id (filename, size, md5sum, sha1sum, sha256sum, location_id):
735 Insert a new entry into the files table and return its id.
737 @type filename: string
738 @param filename: the filename
741 @param size: the size in bytes
744 @param md5sum: md5sum of the file
746 @type sha1sum: string
747 @param sha1sum: sha1sum of the file
749 @type sha256sum: string
750 @param sha256sum: sha256sum of the file
752 @type location_id: int
753 @param location_id: the id of the location as returned by L{get_location_id}
756 @return: the database id for the new file
759 global files_id_cache
761 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))
763 return get_files_id (filename, size, md5sum, location_id)
765 ### currval has issues with postgresql 7.1.3 when the table is big
766 ### it was taking ~3 seconds to return on auric which is very Not
769 ##q = projectB.query("SELECT id FROM files WHERE id = currval('files_id_seq')")
770 ##ql = q.getresult()[0]
771 ##cache_key = "%s_%d" % (filename, location_id)
772 ##files_id_cache[cache_key] = ql[0]
773 ##return files_id_cache[cache_key]
775 ################################################################################
777 def get_maintainer (maintainer_id):
779 Return the name of the maintainer behind C{maintainer_id}.
781 Results are kept in a cache during runtime to minimize database queries.
783 @type maintainer_id: int
784 @param maintainer_id: the id of the maintainer, eg. from L{get_or_set_maintainer_id}
787 @return: the name of the maintainer
790 global maintainer_cache
792 if not maintainer_cache.has_key(maintainer_id):
793 q = projectB.query("SELECT name FROM maintainer WHERE id = %s" % (maintainer_id))
794 maintainer_cache[maintainer_id] = q.getresult()[0][0]
796 return maintainer_cache[maintainer_id]
798 ################################################################################
800 def get_suites(pkgname, src=False):
802 Return the suites in which C{pkgname} can be found. If C{src} is True query for source
803 package, else binary package.
805 @type pkgname: string
806 @param pkgname: name of the package
809 @param src: if True look for source packages, false (default) looks for binary.
812 @return: list of suites, or empty list if no match
821 WHERE source.id = src_associations.source
822 AND source.source = '%s'
823 AND src_associations.suite = suite.id
831 WHERE binaries.id = bin_associations.bin
833 AND bin_associations.suite = suite.id
836 q = projectB.query(sql)
837 return map(lambda x: x[0], q.getresult())
840 ################################################################################
842 def get_new_comments(package):
844 Returns all the possible comments attached to C{package} in NEW. All versions.
846 @type package: string
847 @param package: name of the package
850 @return: list of strings containing comments for all versions from all authors for package
854 query = projectB.query(""" SELECT version, comment, author, notedate
860 for row in query.getresult():
861 comments.append("\nAuthor: %s\nVersion: %s\nTimestamp: %s\n\n%s\n" % (row[2], row[0], row[3], row[1]))
862 comments.append("-"*72)
866 def has_new_comment(package, version):
868 Returns true if the given combination of C{package}, C{version} has a comment.
870 @type package: string
871 @param package: name of the package
873 @type version: string
874 @param version: package version
880 exists = projectB.query("""SELECT 1 FROM new_comments
884 % (package, version) ).getresult()
891 def add_new_comment(package, version, comment, author):
893 Add a new comment for C{package}, C{version} written by C{author}
895 @type package: string
896 @param package: name of the package
898 @type version: string
899 @param version: package version
901 @type comment: string
902 @param comment: the comment
905 @param author: the authorname
908 projectB.query(""" INSERT INTO new_comments (package, version, comment, author)
909 VALUES ('%s', '%s', '%s', '%s')
910 """ % (package, version, comment, author) )
914 def delete_new_comments(package, version):
916 Delete a comment for C{package}, C{version}, if one exists
919 projectB.query(""" DELETE FROM new_comments
920 WHERE package = '%s' AND version = '%s'
921 """ % (package, version))
924 ################################################################################
925 def copy_temporary_contents(package, version, arch, deb, reject):
927 copy the previously stored contents from the temp table to the permanant one
929 during process-unchecked, the deb should have been scanned and the
930 contents stored in pending_content_associations
933 # first see if contents exist:
935 arch_id = get_architecture_id (arch)
937 exists = projectB.query("""SELECT 1 FROM pending_content_associations
940 AND architecture=%d LIMIT 1"""
941 % (package, version, arch_id) ).getresult()
944 # This should NOT happen. We should have added contents
945 # during process-unchecked. if it did, log an error, and send
948 "__PACKAGE__": package,
949 "__VERSION__": version,
951 "__TO_ADDRESS__": Cnf["Dinstall::MyAdminAddress"],
952 "__DAK_ADDRESS__": Cnf["Dinstall::MyEmailAddress"] }
954 message = utils.TemplateSubst(subst, Cnf["Dir::Templates"]+"/missing-contents")
955 utils.send_mail( message )
957 exists = Binary(deb, reject).scan_package()
960 sql = """INSERT INTO content_associations(binary_pkg,filepath,filename)
961 SELECT currval('binaries_id_seq'), filepath, filename FROM pending_content_associations
964 AND architecture=%d""" % (package, version, arch_id)
966 projectB.query("""DELETE from pending_content_associations
969 AND architecture=%d""" % (package, version, arch_id))