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 ################################################################################
22 import os, sys, time, types, apt_pkg
24 ################################################################################
30 priority_id_cache = {}
31 override_type_id_cache = {}
32 architecture_id_cache = {}
34 component_id_cache = {}
35 location_id_cache = {}
36 maintainer_id_cache = {}
41 fingerprint_id_cache = {}
44 suite_version_cache = {}
45 suite_bin_version_cache = {}
46 content_path_id_cache = {}
47 content_file_id_cache = {}
48 insert_contents_file_cache = {}
49 cache_preloaded = False
51 ################################################################################
53 def init (config, sql):
61 sys.stderr.write("query: \"%s\" ... " % (q))
64 time_diff = time.time()-before
65 sys.stderr.write("took %.3f seconds.\n" % (time_diff))
67 sys.stderr.write("int result: %s\n" % (r))
68 elif type(r) is types.NoneType:
69 sys.stderr.write("result: None\n")
71 sys.stderr.write("pgresult: %s\n" % (r.getresult()))
74 ################################################################################
76 def get_suite_id (suite):
79 if suite_id_cache.has_key(suite):
80 return suite_id_cache[suite]
82 q = projectB.query("SELECT id FROM suite WHERE suite_name = '%s'" % (suite))
88 suite_id_cache[suite] = suite_id
92 def get_section_id (section):
93 global section_id_cache
95 if section_id_cache.has_key(section):
96 return section_id_cache[section]
98 q = projectB.query("SELECT id FROM section WHERE section = '%s'" % (section))
103 section_id = ql[0][0]
104 section_id_cache[section] = section_id
108 def get_priority_id (priority):
109 global priority_id_cache
111 if priority_id_cache.has_key(priority):
112 return priority_id_cache[priority]
114 q = projectB.query("SELECT id FROM priority WHERE priority = '%s'" % (priority))
119 priority_id = ql[0][0]
120 priority_id_cache[priority] = priority_id
124 def get_override_type_id (type):
125 global override_type_id_cache
127 if override_type_id_cache.has_key(type):
128 return override_type_id_cache[type]
130 q = projectB.query("SELECT id FROM override_type WHERE type = '%s'" % (type))
135 override_type_id = ql[0][0]
136 override_type_id_cache[type] = override_type_id
138 return override_type_id
140 def get_architecture_id (architecture):
141 global architecture_id_cache
143 if architecture_id_cache.has_key(architecture):
144 return architecture_id_cache[architecture]
146 q = projectB.query("SELECT id FROM architecture WHERE arch_string = '%s'" % (architecture))
151 architecture_id = ql[0][0]
152 architecture_id_cache[architecture] = architecture_id
154 return architecture_id
156 def get_archive_id (archive):
157 global archive_id_cache
159 archive = archive.lower()
161 if archive_id_cache.has_key(archive):
162 return archive_id_cache[archive]
164 q = projectB.query("SELECT id FROM archive WHERE lower(name) = '%s'" % (archive))
169 archive_id = ql[0][0]
170 archive_id_cache[archive] = archive_id
174 def get_component_id (component):
175 global component_id_cache
177 component = component.lower()
179 if component_id_cache.has_key(component):
180 return component_id_cache[component]
182 q = projectB.query("SELECT id FROM component WHERE lower(name) = '%s'" % (component))
187 component_id = ql[0][0]
188 component_id_cache[component] = component_id
192 def get_location_id (location, component, archive):
193 global location_id_cache
195 cache_key = location + '_' + component + '_' + location
196 if location_id_cache.has_key(cache_key):
197 return location_id_cache[cache_key]
199 archive_id = get_archive_id (archive)
201 component_id = get_component_id (component)
202 if component_id != -1:
203 q = projectB.query("SELECT id FROM location WHERE path = '%s' AND component = %d AND archive = %d" % (location, component_id, archive_id))
205 q = projectB.query("SELECT id FROM location WHERE path = '%s' AND archive = %d" % (location, archive_id))
210 location_id = ql[0][0]
211 location_id_cache[cache_key] = location_id
215 def get_source_id (source, version):
216 global source_id_cache
218 cache_key = source + '_' + version + '_'
219 if source_id_cache.has_key(cache_key):
220 return source_id_cache[cache_key]
222 q = projectB.query("SELECT id FROM source s WHERE s.source = '%s' AND s.version = '%s'" % (source, version))
224 if not q.getresult():
227 source_id = q.getresult()[0][0]
228 source_id_cache[cache_key] = source_id
232 def get_suite_version(source, suite, arch):
233 global suite_version_cache
234 cache_key = "%s_%s" % (source, suite)
236 if suite_version_cache.has_key(cache_key):
237 return suite_version_cache[cache_key]
239 q = projectB.query("""
240 SELECT s.version FROM source s, suite su, src_associations sa
243 AND su.suite_name='%s'
247 if not q.getresult():
250 version = q.getresult()[0][0]
251 suite_version_cache[cache_key] = version
255 def get_latest_binary_version_id(binary, section, suite, arch):
256 global suite_bin_version_cache
257 cache_key = "%s_%s_%s_%s" % (binary, section, suite, arch)
258 cache_key_all = "%s_%s_%s_%s" % (binary, section, suite, get_architecture_id("all"))
260 # Check for the cache hit for its arch, then arch all
261 if suite_bin_version_cache.has_key(cache_key):
262 return suite_bin_version_cache[cache_key]
263 if suite_bin_version_cache.has_key(cache_key_all):
264 return suite_bin_version_cache[cache_key_all]
265 if cache_preloaded == True:
266 return # package does not exist
268 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)))
270 if not q.getresult():
273 highest_bid = q.getresult()[0][0]
275 suite_bin_version_cache[cache_key] = highest_bid
278 def preload_binary_id_cache():
279 global suite_bin_version_cache, cache_preloaded
282 q = projectB.query("SELECT id FROM suite")
283 suites = q.getresult()
286 q = projectB.query("SELECT id FROM architecture")
287 arches = q.getresult()
291 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])))
293 for bi in q.getresult():
294 cache_key = "%s_%s_%s_%s" % (bi[1], bi[2], suite[0], arch[0])
295 suite_bin_version_cache[cache_key] = int(bi[0])
297 cache_preloaded = True
299 ################################################################################
301 def get_or_set_maintainer_id (maintainer):
302 global maintainer_id_cache
304 if maintainer_id_cache.has_key(maintainer):
305 return maintainer_id_cache[maintainer]
307 q = projectB.query("SELECT id FROM maintainer WHERE name = '%s'" % (maintainer))
308 if not q.getresult():
309 projectB.query("INSERT INTO maintainer (name) VALUES ('%s')" % (maintainer))
310 q = projectB.query("SELECT id FROM maintainer WHERE name = '%s'" % (maintainer))
311 maintainer_id = q.getresult()[0][0]
312 maintainer_id_cache[maintainer] = maintainer_id
316 ################################################################################
318 def get_or_set_keyring_id (keyring):
319 global keyring_id_cache
321 if keyring_id_cache.has_key(keyring):
322 return keyring_id_cache[keyring]
324 q = projectB.query("SELECT id FROM keyrings WHERE name = '%s'" % (keyring))
325 if not q.getresult():
326 projectB.query("INSERT INTO keyrings (name) VALUES ('%s')" % (keyring))
327 q = projectB.query("SELECT id FROM keyrings WHERE name = '%s'" % (keyring))
328 keyring_id = q.getresult()[0][0]
329 keyring_id_cache[keyring] = keyring_id
333 ################################################################################
335 def get_or_set_uid_id (uid):
338 if uid_id_cache.has_key(uid):
339 return uid_id_cache[uid]
341 q = projectB.query("SELECT id FROM uid WHERE uid = '%s'" % (uid))
342 if not q.getresult():
343 projectB.query("INSERT INTO uid (uid) VALUES ('%s')" % (uid))
344 q = projectB.query("SELECT id FROM uid WHERE uid = '%s'" % (uid))
345 uid_id = q.getresult()[0][0]
346 uid_id_cache[uid] = uid_id
350 ################################################################################
352 def get_or_set_fingerprint_id (fingerprint):
353 global fingerprint_id_cache
355 if fingerprint_id_cache.has_key(fingerprint):
356 return fingerprint_id_cache[fingerprint]
358 q = projectB.query("SELECT id FROM fingerprint WHERE fingerprint = '%s'" % (fingerprint))
359 if not q.getresult():
360 projectB.query("INSERT INTO fingerprint (fingerprint) VALUES ('%s')" % (fingerprint))
361 q = projectB.query("SELECT id FROM fingerprint WHERE fingerprint = '%s'" % (fingerprint))
362 fingerprint_id = q.getresult()[0][0]
363 fingerprint_id_cache[fingerprint] = fingerprint_id
365 return fingerprint_id
367 ################################################################################
369 def get_files_id (filename, size, md5sum, location_id):
370 global files_id_cache
372 cache_key = "%s_%d" % (filename, location_id)
374 if files_id_cache.has_key(cache_key):
375 return files_id_cache[cache_key]
378 q = projectB.query("SELECT id, size, md5sum FROM files WHERE filename = '%s' AND location = %d" % (filename, location_id))
384 orig_size = int(ql[1])
386 if orig_size != size or orig_md5sum != md5sum:
388 files_id_cache[cache_key] = ql[0]
389 return files_id_cache[cache_key]
393 ################################################################################
395 def get_or_set_queue_id (queue):
396 global queue_id_cache
398 if queue_id_cache.has_key(queue):
399 return queue_id_cache[queue]
401 q = projectB.query("SELECT id FROM queue WHERE queue_name = '%s'" % (queue))
402 if not q.getresult():
403 projectB.query("INSERT INTO queue (queue_name) VALUES ('%s')" % (queue))
404 q = projectB.query("SELECT id FROM queue WHERE queue_name = '%s'" % (queue))
405 queue_id = q.getresult()[0][0]
406 queue_id_cache[queue] = queue_id
410 ################################################################################
412 def set_files_id (filename, size, md5sum, sha1sum, sha256sum, location_id):
413 global files_id_cache
415 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))
417 return get_files_id (filename, size, md5sum, location_id)
419 ### currval has issues with postgresql 7.1.3 when the table is big
420 ### it was taking ~3 seconds to return on auric which is very Not
423 ##q = projectB.query("SELECT id FROM files WHERE id = currval('files_id_seq')")
424 ##ql = q.getresult()[0]
425 ##cache_key = "%s_%d" % (filename, location_id)
426 ##files_id_cache[cache_key] = ql[0]
427 ##return files_id_cache[cache_key]
429 ################################################################################
431 def get_maintainer (maintainer_id):
432 global maintainer_cache
434 if not maintainer_cache.has_key(maintainer_id):
435 q = projectB.query("SELECT name FROM maintainer WHERE id = %s" % (maintainer_id))
436 maintainer_cache[maintainer_id] = q.getresult()[0][0]
438 return maintainer_cache[maintainer_id]
440 ################################################################################
442 def get_suites(pkgname, src=False):
444 sql = "select suite_name from source, src_associations,suite where source.id=src_associations.source and source.source='%s' and src_associations.suite = suite.id"%pkgname
446 sql = "select suite_name from binaries, bin_associations,suite where binaries.id=bin_associations.bin and package='%s' and bin_associations.suite = suite.id"%pkgname
447 q = projectB.query(sql)
448 return map(lambda x: x[0], q.getresult())
450 ################################################################################
452 def get_or_set_contents_file_id(file):
453 global content_file_id_cache
455 if not content_file_id_cache.has_key(file):
456 sql_select = "SELECT id FROM content_file_names WHERE file = '%s'" % file
457 q = projectB.query(sql_select)
458 if not q.getresult():
459 # since this can be called within a transaction, we can't use currval
460 q = projectB.query("INSERT INTO content_file_names VALUES (DEFAULT, '%s') RETURNING id" % (file))
461 content_file_id_cache[file] = int(q.getresult()[0][0])
462 return content_file_id_cache[file]
464 ################################################################################
466 def get_or_set_contents_path_id(path):
467 global content_path_id_cache
469 if not content_path_id_cache.has_key(path):
470 sql_select = "SELECT id FROM content_file_paths WHERE path = '%s'" % path
471 q = projectB.query(sql_select)
472 if not q.getresult():
473 # since this can be called within a transaction, we can't use currval
474 q = projectB.query("INSERT INTO content_file_paths VALUES (DEFAULT, '%s') RETURNING id" % (path))
475 content_path_id_cache[path] = int(q.getresult()[0][0])
476 return content_path_id_cache[path]
478 ################################################################################
480 def insert_content_path(bin_id, fullpath):
481 global insert_contents_file_cache
482 cache_key = "%s_%s" % (bin_id, fullpath)
484 # have we seen this contents before?
485 # probably only revelant during package import
486 if insert_contents_file_cache.has_key(cache_key):
489 # split the path into basename, and pathname
490 (path, file) = os.path.split(fullpath)
492 # Get the necessary IDs ...
493 file_id = get_or_set_contents_file_id(file)
494 path_id = get_or_set_contents_path_id(path)
496 # Determine if we're inserting a duplicate row
497 q = projectB.query("SELECT 1 FROM content_associations WHERE binary_pkg = '%d' AND filepath = '%d' AND filename = '%d'" % (int(bin_id), path_id, file_id))
499 # Yes we are, return without doing the insert
502 # Put them into content_assiocations
503 projectB.query("INSERT INTO content_associations VALUES (DEFAULT, '%d', '%d', '%d')" % (bin_id, path_id, file_id))