4 # Copyright (C) 2000, 2001, 2002, 2003, 2004, 2006 James Troup <james@nocrew.org>
6 # This program is free software; you can redistribute it and/or modify
7 # it under the terms of the GNU General Public License as published by
8 # the Free Software Foundation; either version 2 of the License, or
9 # (at your option) any later version.
11 # This program is distributed in the hope that it will be useful,
12 # but WITHOUT ANY WARRANTY; without even the implied warranty of
13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 # GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License
17 # along with this program; if not, write to the Free Software
18 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20 ################################################################################
26 ################################################################################
32 priority_id_cache = {}
33 override_type_id_cache = {}
34 architecture_id_cache = {}
36 component_id_cache = {}
37 location_id_cache = {}
38 maintainer_id_cache = {}
43 fingerprint_id_cache = {}
46 suite_version_cache = {}
48 ################################################################################
50 def init (config, sql):
51 """ database module init. Just sets two variables"""
60 Executes a database query q. Writes statistics to stderr and returns
64 sys.stderr.write("query: \"%s\" ... " % (q))
67 time_diff = time.time()-before
68 sys.stderr.write("took %.3f seconds.\n" % (time_diff))
70 sys.stderr.write("int result: %s\n" % (r))
71 elif type(r) is types.NoneType:
72 sys.stderr.write("result: None\n")
74 sys.stderr.write("pgresult: %s\n" % (r.getresult()))
77 ################################################################################
79 def get_suite_id (suite):
80 """ Returns database suite_id for given suite, caches result. """
83 if suite_id_cache.has_key(suite):
84 return suite_id_cache[suite]
86 q = projectB.query("SELECT id FROM suite WHERE suite_name = '%s'" % (suite))
92 suite_id_cache[suite] = suite_id
96 def get_section_id (section):
97 """ Returns database section_id for given section, caches result. """
98 global section_id_cache
100 if section_id_cache.has_key(section):
101 return section_id_cache[section]
103 q = projectB.query("SELECT id FROM section WHERE section = '%s'" % (section))
108 section_id = ql[0][0]
109 section_id_cache[section] = section_id
113 def get_priority_id (priority):
114 """ Returns database priority_id for given priority, caches result. """
115 global priority_id_cache
117 if priority_id_cache.has_key(priority):
118 return priority_id_cache[priority]
120 q = projectB.query("SELECT id FROM priority WHERE priority = '%s'" % (priority))
125 priority_id = ql[0][0]
126 priority_id_cache[priority] = priority_id
130 def get_override_type_id (type):
131 """ Returns database override_id for given override_type type, caches result. """
132 global override_type_id_cache
134 if override_type_id_cache.has_key(type):
135 return override_type_id_cache[type]
137 q = projectB.query("SELECT id FROM override_type WHERE type = '%s'" % (type))
142 override_type_id = ql[0][0]
143 override_type_id_cache[type] = override_type_id
145 return override_type_id
147 def get_architecture_id (architecture):
148 """ Returns database architecture_id for given architecture, caches result. """
149 global architecture_id_cache
151 if architecture_id_cache.has_key(architecture):
152 return architecture_id_cache[architecture]
154 q = projectB.query("SELECT id FROM architecture WHERE arch_string = '%s'" % (architecture))
159 architecture_id = ql[0][0]
160 architecture_id_cache[architecture] = architecture_id
162 return architecture_id
164 def get_archive_id (archive):
165 """ Returns database archive_id for given archive, caches result. """
166 global archive_id_cache
168 archive = archive.lower()
170 if archive_id_cache.has_key(archive):
171 return archive_id_cache[archive]
173 q = projectB.query("SELECT id FROM archive WHERE lower(name) = '%s'" % (archive))
178 archive_id = ql[0][0]
179 archive_id_cache[archive] = archive_id
183 def get_component_id (component):
184 """ Returns database component_id for given component, caches result. """
185 global component_id_cache
187 component = component.lower()
189 if component_id_cache.has_key(component):
190 return component_id_cache[component]
192 q = projectB.query("SELECT id FROM component WHERE lower(name) = '%s'" % (component))
197 component_id = ql[0][0]
198 component_id_cache[component] = component_id
202 def get_location_id (location, component, archive):
204 Returns database location_id for given combination of
209 The 3 parameters are the database ids returned by the respective
210 "get_foo_id" functions.
212 The result will be cached.
215 global location_id_cache
217 cache_key = location + '_' + component + '_' + location
218 if location_id_cache.has_key(cache_key):
219 return location_id_cache[cache_key]
221 archive_id = get_archive_id (archive)
223 component_id = get_component_id (component)
224 if component_id != -1:
225 q = projectB.query("SELECT id FROM location WHERE path = '%s' AND component = %d AND archive = %d" % (location, component_id, archive_id))
227 q = projectB.query("SELECT id FROM location WHERE path = '%s' AND archive = %d" % (location, archive_id))
232 location_id = ql[0][0]
233 location_id_cache[cache_key] = location_id
237 def get_source_id (source, version):
238 """ Returns database source_id for given combination of source and version, caches result. """
239 global source_id_cache
241 cache_key = source + '_' + version + '_'
242 if source_id_cache.has_key(cache_key):
243 return source_id_cache[cache_key]
245 q = projectB.query("SELECT id FROM source s WHERE s.source = '%s' AND s.version = '%s'" % (source, version))
247 if not q.getresult():
250 source_id = q.getresult()[0][0]
251 source_id_cache[cache_key] = source_id
255 def get_suite_version(source, suite):
256 """ Returns database version for a given source in a given suite, caches result. """
257 global suite_version_cache
258 cache_key = "%s_%s" % (source, suite)
260 if suite_version_cache.has_key(cache_key):
261 return suite_version_cache[cache_key]
263 q = projectB.query("""
264 SELECT s.version FROM source s, suite su, src_associations sa
267 AND su.suite_name='%s'
271 if not q.getresult():
274 version = q.getresult()[0][0]
275 suite_version_cache[cache_key] = version
279 ################################################################################
281 def get_or_set_maintainer_id (maintainer):
283 If maintainer does not have an entry in the maintainer table yet, create one
285 If maintainer already has an entry, simply return its id.
290 global maintainer_id_cache
292 if maintainer_id_cache.has_key(maintainer):
293 return maintainer_id_cache[maintainer]
295 q = projectB.query("SELECT id FROM maintainer WHERE name = '%s'" % (maintainer))
296 if not q.getresult():
297 projectB.query("INSERT INTO maintainer (name) VALUES ('%s')" % (maintainer))
298 q = projectB.query("SELECT id FROM maintainer WHERE name = '%s'" % (maintainer))
299 maintainer_id = q.getresult()[0][0]
300 maintainer_id_cache[maintainer] = maintainer_id
304 ################################################################################
306 def get_or_set_keyring_id (keyring):
308 If keyring does not have an entry in the keyring table yet, create one
310 If keyring already has an entry, simply return its id.
315 global keyring_id_cache
317 if keyring_id_cache.has_key(keyring):
318 return keyring_id_cache[keyring]
320 q = projectB.query("SELECT id FROM keyrings WHERE name = '%s'" % (keyring))
321 if not q.getresult():
322 projectB.query("INSERT INTO keyrings (name) VALUES ('%s')" % (keyring))
323 q = projectB.query("SELECT id FROM keyrings WHERE name = '%s'" % (keyring))
324 keyring_id = q.getresult()[0][0]
325 keyring_id_cache[keyring] = keyring_id
329 ################################################################################
331 def get_or_set_uid_id (uid):
333 If uid does not have an entry in the uid table yet, create one
335 If uid already has an entry, simply return its id.
342 if uid_id_cache.has_key(uid):
343 return uid_id_cache[uid]
345 q = projectB.query("SELECT id FROM uid WHERE uid = '%s'" % (uid))
346 if not q.getresult():
347 projectB.query("INSERT INTO uid (uid) VALUES ('%s')" % (uid))
348 q = projectB.query("SELECT id FROM uid WHERE uid = '%s'" % (uid))
349 uid_id = q.getresult()[0][0]
350 uid_id_cache[uid] = uid_id
354 ################################################################################
356 def get_or_set_fingerprint_id (fingerprint):
358 If fingerprintd does not have an entry in the fingerprint table yet, create one
360 If fingerprint already has an entry, simply return its id.
365 global fingerprint_id_cache
367 if fingerprint_id_cache.has_key(fingerprint):
368 return fingerprint_id_cache[fingerprint]
370 q = projectB.query("SELECT id FROM fingerprint WHERE fingerprint = '%s'" % (fingerprint))
371 if not q.getresult():
372 projectB.query("INSERT INTO fingerprint (fingerprint) VALUES ('%s')" % (fingerprint))
373 q = projectB.query("SELECT id FROM fingerprint WHERE fingerprint = '%s'" % (fingerprint))
374 fingerprint_id = q.getresult()[0][0]
375 fingerprint_id_cache[fingerprint] = fingerprint_id
377 return fingerprint_id
379 ################################################################################
381 def get_files_id (filename, size, md5sum, location_id):
383 Returns -1, -2 or the file_id for a given combination of
389 The database is queried using filename and location_id, size and md5sum are for
393 -1 - The given combination of arguments result in more (or less) than
394 one result from the database
395 -2 - The given size and md5sum do not match the values in the database
396 anything else is a file_id
401 global files_id_cache
403 cache_key = "%s_%d" % (filename, location_id)
405 if files_id_cache.has_key(cache_key):
406 return files_id_cache[cache_key]
409 q = projectB.query("SELECT id, size, md5sum FROM files WHERE filename = '%s' AND location = %d" % (filename, location_id))
415 orig_size = int(ql[1])
417 if orig_size != size or orig_md5sum != md5sum:
419 files_id_cache[cache_key] = ql[0]
420 return files_id_cache[cache_key]
424 ################################################################################
426 def get_or_set_queue_id (queue):
428 If queue does not have an entry in the queue_name table yet, create one
430 If queue already has an entry, simply return its id.
435 global queue_id_cache
437 if queue_id_cache.has_key(queue):
438 return queue_id_cache[queue]
440 q = projectB.query("SELECT id FROM queue WHERE queue_name = '%s'" % (queue))
441 if not q.getresult():
442 projectB.query("INSERT INTO queue (queue_name) VALUES ('%s')" % (queue))
443 q = projectB.query("SELECT id FROM queue WHERE queue_name = '%s'" % (queue))
444 queue_id = q.getresult()[0][0]
445 queue_id_cache[queue] = queue_id
449 ################################################################################
451 def set_files_id (filename, size, md5sum, sha1sum, sha256sum, location_id):
453 Insert a new entry into the files table.
455 Returns the new file_id
458 global files_id_cache
460 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))
462 return get_files_id (filename, size, md5sum, location_id)
464 ### currval has issues with postgresql 7.1.3 when the table is big
465 ### it was taking ~3 seconds to return on auric which is very Not
468 ##q = projectB.query("SELECT id FROM files WHERE id = currval('files_id_seq')")
469 ##ql = q.getresult()[0]
470 ##cache_key = "%s_%d" % (filename, location_id)
471 ##files_id_cache[cache_key] = ql[0]
472 ##return files_id_cache[cache_key]
474 ################################################################################
476 def get_maintainer (maintainer_id):
477 """ Return the name of the maintainer behind maintainer_id """
478 global maintainer_cache
480 if not maintainer_cache.has_key(maintainer_id):
481 q = projectB.query("SELECT name FROM maintainer WHERE id = %s" % (maintainer_id))
482 maintainer_cache[maintainer_id] = q.getresult()[0][0]
484 return maintainer_cache[maintainer_id]
486 ################################################################################
488 def get_suites(pkgname, src=False):
489 """ Return the suites in which pkgname is. If src is True, query for source package, else binary. """
496 WHERE source.id = src_associations.source
497 AND source.source = '%s'
498 AND src_associations.suite = suite.id
506 WHERE binaries.id = bin_associations.bin
508 AND bin_associations.suite = suite.id
511 q = projectB.query(sql)
512 return map(lambda x: x[0], q.getresult())