def _load_repodata(self): """Load the repo data""" with self._named_cursor() as cursor: cursor.execute("""select r.id, cs.label, cs.name, r.basearch_id, r.releasever, r.revision from repo r join content_set cs on cs.id = r.content_set_id """) for repo_id, label, name, arch_id, releasever, revision in cursor: archname = '' if arch_id in self.archid2arch: archname = self.archid2arch[arch_id] self.repodata[repo_id] = { 'revision': revision, 'data': { 'label': label, 'name': name, 'arch': archname, 'releasever': releasever, 'revision': format_datetime(revision) } }
def main(): """ Main loop.""" init_logging() init_db() db_instance = DatabaseHandler.get_connection() timestamp = format_datetime(now()) data = SqliteDump(db_instance, DUMP) data.dump(timestamp)
def _save_lastmodified(self, lastmodified): lastmodified = format_datetime(lastmodified) cur = self.conn.cursor() # Update timestamp cur.execute("update metadata set value = %s where key = %s", (lastmodified, self.UPDATED_KEY,)) if cur.rowcount < 1: cur.execute("insert into metadata (key, value) values (%s, %s)", (self.UPDATED_KEY, lastmodified)) cur.close() self.conn.commit()
def _dump_repo(self, dump): """Select repo mappings""" dump.execute("""create table if not exists repo_detail ( id integer primary key, label text, name text, url text, basearch text, releasever text, product text, product_id integer, revision text, third_party integer )""") # Select repo detail mapping with self._named_cursor() as cursor: cursor.execute("""select r.id, cs.label, cs.name as repo_name, r.url, a.name as basearch_name, r.releasever, p.name as product_name, p.id as product_id, r.revision, cs.third_party from repo r join content_set cs on cs.id = r.content_set_id left join arch a on a.id = r.basearch_id join product p on p.id = cs.product_id """) for oid, label, name, url, basearch, releasever, \ product, product_id, revision, third_party in cursor: dump.execute("insert into repo_detail values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (oid, label, name, url, basearch, releasever, product, product_id, # Use NULLs in export format_datetime(revision) if revision is not None else None, 1 if third_party else 0),) dump.execute("""create table if not exists pkg_repo ( pkg_id integer, repo_id integer, primary key (pkg_id, repo_id) ) without rowid""") if self.package_ids: # Select package ID to repo IDs mapping with self._named_cursor() as cursor: cursor.execute("""select pkg_id, repo_id from pkg_repo where pkg_id in %s """, [tuple(self.package_ids)]) for pkg_id, repo_id in cursor: dump.execute("insert into pkg_repo values (?, ?)", (pkg_id, repo_id))
def _load_errata(self): """Load the errata data""" with self._named_cursor() as cursor: cursor.execute("select id, name, issued from errata") for errata_id, name, issued in cursor: self.erratadata[errata_id] = { 'issued': issued, 'data': { 'name': name, 'issued': format_datetime(issued) } }
def _dump_dbchange(self, dump, timestamp): """Select db change details""" dump.execute("""create table if not exists dbchange ( errata_changes text, cve_changes text, repository_changes text, last_change text, exported text )""") with self._named_cursor() as cursor: cursor.execute("""select errata_changes, cve_changes, repository_changes, last_change from dbchange""") row = cursor.fetchone() dump.execute( "insert into dbchange values (?, ?, ?, ?, ?)", (format_datetime(row[0]) if row[0] is not None else None, format_datetime(row[1]) if row[1] is not None else None, format_datetime(row[2]) if row[2] is not None else None, format_datetime(row[3]) if row[3] is not None else None, timestamp))
def dump(self): """Dump necessary data to disk file""" starttime = now() timestamp = format_datetime(starttime) self._update_pkgtree_timestamp(timestamp) dump_filename = '%s-%s' % (self.filename, timestamp) self.outputdata['timestamp'] = timestamp self.outputdata['packages'] = {} LOGGER.info("Loading pkgtree data") try: self._load_packagenames() self._load_evr() self._load_arch() self._load_repodata() self._load_cves() self._load_errata() self._associate_cves_to_errata() self._load_packages() self._load_module_streams() self._load_modules() self._associate_modules() self._associate_repos() self._associate_errata() except Exception: # pylint: disable=broad-except # database exceptions caught here LOGGER.exception("Failed to export pkgtree") else: # only write pkgtree if all db queries succeeded LOGGER.info("Exporting data to %s", dump_filename) with gzip.open(dump_filename, 'wt') as dump_file: json.dump(self.outputdata, dump_file, indent=self.pkgtree_indent, ensure_ascii=False) # relink to the latest file remove_file_if_exists(self.filename) os.symlink(dump_filename, self.filename) LOGGER.info("Finished exporting data. Elapsed time: %s", now() - starttime) # remove old data above limit old_data = sorted(glob.glob("%s-*" % self.filename), reverse=True) for fname in old_data[self.pkgtree_keep_copies:]: LOGGER.info("Removing old dump %s", fname) remove_file_if_exists(fname)
def _dump_cves(self, dump): """Select cve details""" dump.execute("""create table if not exists cve_cwe ( cve_id integer, cwe text )""") dump.execute("""create table if not exists cve_pkg ( cve_id integer, pkg_id integer )""") dump.execute("""create table if not exists cve_detail ( id integer primary key , name text, redhat_url text, secondary_url text, cvss3_score float, cvss3_metrics text, impact text, published_date text, modified_date text, iava text, description text, cvss2_score float, cvss2_metrics text, source text )""") # Select CWE to CVE mapping with self._named_cursor() as cursor: cursor.execute("""select cve_id, cwe.name from cve_cwe join cwe on cve_cwe.cwe_id = cwe.id """) for cve_id, cwe in cursor: dump.execute("insert into cve_cwe values (?, ?)", (cve_id, cwe)) # Select CVE to package-id mapping with self._named_cursor() as cursor: cursor.execute(""" select distinct cve.id as cve_id, pe.pkg_id from cve cve inner join errata_cve ec on cve.id = ec.cve_id inner join pkg_errata pe on ec.errata_id = pe.errata_id order by cve.id, pe.pkg_id """) for cve_id, pkg_id in cursor: dump.execute("insert into cve_pkg values (?, ?)", (cve_id, pkg_id)) # Pull everything together with self._named_cursor() as cursor: cursor.execute("""select cve.id, cve.name, cve.redhat_url, cve.secondary_url, cve.cvss3_score, cve.cvss3_metrics, cve_impact.name as impact, cve.published_date, cve.modified_date, cve.iava, cve.description, cve.cvss2_score, cve.cvss2_metrics, cve_source.name as source from cve join cve_source on cve.source_id = cve_source.id left join cve_impact on cve.impact_id = cve_impact.id """) for cve_id, name, redhat_url, secondary_url, \ cvss3_score, cvss3_metrics, \ impact, published_date, modified_date, iava, description, \ cvss2_score, cvss2_metrics, source in cursor: cvss3_score = (float(cvss3_score) if cvss3_score is not None else None) cvss2_score = (float(cvss2_score) if cvss2_score is not None else None) dump.execute( "insert into cve_detail values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (cve_id, name, redhat_url, secondary_url, cvss3_score, cvss3_metrics, impact, format_datetime(published_date) if published_date is not None else None, format_datetime(modified_date) if modified_date is not None else None, iava, description, cvss2_score, cvss2_metrics, source))
def _dump_errata(self, dump): # pylint: disable=too-many-branches """Select errata mappings""" # Select errata ID to name mapping dump.execute("""create table if not exists errata_detail ( id integer primary key, name text, synopsis text, summary text, type text, severity text, description text, solution text, issued text, updated text, url text, third_party integer, requires_reboot boolean )""") with self._named_cursor() as cursor: cursor.execute("""select distinct e.id from errata e inner join errata_type et on e.errata_type_id = et.id left join errata_cve ec on e.id = ec.errata_id """) for errata_id in cursor: self.errata_ids.append(errata_id) dump.execute("""create table if not exists pkg_errata ( pkg_id integer, errata_id integer, primary key (pkg_id, errata_id) ) without rowid""") dump.execute("""create table if not exists errata_repo ( errata_id integer, repo_id integer, primary key(errata_id, repo_id) ) without rowid""") dump.execute("""create table if not exists errata_cve ( errata_id integer, cve text, primary key(errata_id, cve) ) without rowid""") dump.execute("""create table if not exists errata_refs ( errata_id integer, ref text )""") dump.execute("""create table if not exists errata_bugzilla ( errata_id integer, bugzilla text )""") dump.execute("""create table if not exists errata_module ( errata_id integer, module_name text, module_stream_id integer, module_stream text, module_version integer, module_context text )""") dump.execute("""create table if not exists errata_modulepkg ( errata_id integer, module_stream_id integer, pkg_id integer, primary key(errata_id, module_stream_id, pkg_id) )""") if self.errata_ids: # Select package ID to errata IDs mapping, only for relevant errata with self._named_cursor() as cursor: cursor.execute( """select pkg_id, errata_id from pkg_errata where errata_id in %s """, [tuple(self.errata_ids)]) for pkg_id, errata_id in cursor: dump.execute( "insert or ignore into pkg_errata values (?, ?)", (pkg_id, errata_id)) # Select errata ID to repo IDs mapping, only for relevant errata with self._named_cursor() as cursor: cursor.execute( """select errata_id, repo_id from errata_repo where errata_id in %s """, [tuple(self.errata_ids)]) for errata_id, repo_id in cursor: dump.execute( "insert or ignore into errata_repo values (?, ?)", (errata_id, repo_id)) # Select errata detail for errata API with self._named_cursor() as cursor: cursor.execute( """SELECT errata_cve.errata_id, cve.name FROM cve JOIN errata_cve ON cve_id = cve.id WHERE errata_id in %s """, [tuple(self.errata_ids)]) for errata_id, cve_name in cursor: dump.execute( "insert or ignore into errata_cve values (?, ?)", (errata_id, cve_name)) with self._named_cursor() as cursor: cursor.execute( """SELECT errata_id, type, name FROM errata_refs WHERE errata_id in %s """, [tuple(self.errata_ids)]) for errata_id, ref_type, ref_name in cursor: if ref_type == 'bugzilla': dump.execute( "insert into errata_bugzilla values (?, ?)", (errata_id, ref_name)) else: dump.execute("insert into errata_refs values (?, ?)", (errata_id, ref_name)) # Select errata ID to module mapping with self._named_cursor() as cursor: cursor.execute( """SELECT distinct p.errata_id, module.name, m.id, m.stream_name, m.version, m.context FROM module_stream m LEFT JOIN module on m.module_id = module.id LEFT JOIN pkg_errata p ON module_stream_id = m.id LEFT JOIN package_name on p.pkg_id = package_name.id WHERE p.module_stream_id IS NOT NULL AND p.errata_id in %s""", [tuple(self.errata_ids)]) for errata_id, module_name, module_stream_id, module_stream_name, \ module_version, module_context in cursor: dump.execute( "insert into errata_module values (?, ?, ?, ?, ?, ?)", (errata_id, module_name, module_stream_id, module_stream_name, module_version, module_context)) # Select module to package ID mapping with self._named_cursor() as cursor: cursor.execute( """SELECT distinct errata_id, module_stream_id, pkg_id FROM pkg_errata WHERE module_stream_id is not null AND errata_id in %s""", [tuple(self.errata_ids)]) for errata_id, module_stream_id, pkg_id in cursor: dump.execute( "insert into errata_modulepkg values (?, ?, ?)", (errata_id, module_stream_id, pkg_id)) # Now pull all the data together for the dump with self._named_cursor() as cursor: cursor.execute( """SELECT errata.id, errata.name, synopsis, summary, errata_type.name, errata_severity.name, description, solution, issued, updated, true = ANY ( SELECT cs.third_party FROM errata_repo er JOIN repo r ON er.repo_id = r.id JOIN content_set cs ON cs.id = r.content_set_id WHERE er.errata_id = errata.id ) AS third_party, requires_reboot FROM errata JOIN errata_type ON errata_type_id = errata_type.id LEFT JOIN errata_severity ON severity_id = errata_severity.id WHERE errata.id in %s """, [tuple(self.errata_ids)]) for errata_id, e_name, synopsis, summary, e_type, e_severity, \ description, solution, issued, updated, third_party, requires_reboot in cursor: url = "https://access.redhat.com/errata/%s" % e_name dump.execute( "insert into errata_detail values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (errata_id, e_name, synopsis, summary, e_type, e_severity, description, solution, format_datetime(issued) if issued is not None else None, format_datetime(updated) if updated is not None else None, url, 1 if third_party else 0, requires_reboot))
def process_list(self, api_version, data): # pylint: disable=unused-argument """ Returns repository details. :param data: json request parsed into data structure :returns: json response with repository details """ repos = data.get('repository_list', None) strip_prefixes(repos, REPO_PREFIXES) modified_since = data.get('modified_since', None) modified_since_dt = parse_datetime(modified_since) page = data.get("page", None) page_size = data.get("page_size", None) # By default, don't include third party data want_third_party = data.get('third_party', False) repolist = {} if not repos: return repolist filters = [] if modified_since: filters.append((self._filter_modified_since, [modified_since_dt])) filters.append((self._filter_third_party, [want_third_party])) repos = self.try_expand_by_regex(repos) repos = list(set(repos)) repo_details = {} for label in repos: for repo_id in self.cache.repolabel2ids.get(label, []): repo_details[label] = self.cache.repo_detail[repo_id] filters.append((filter_item_if_exists, [repo_details])) actual_page_size = 0 repo_page_to_process, pagination_response = paginate(repos, page, page_size, filters=filters) for label in repo_page_to_process: cs_id = self.cache.label2content_set_id[label] for repo_id in self.cache.repolabel2ids.get(label, []): repo_detail = self.cache.repo_detail[repo_id] if not modified_since_dt or self._modified_since( repo_detail, modified_since_dt): if repo_id in self.cache.repo_id2cpe_ids: cpe_ids = self.cache.repo_id2cpe_ids[repo_id] else: cpe_ids = self.cache.content_set_id2cpe_ids.get( cs_id, []) repolist.setdefault(label, []).append({ "label": label, "name": repo_detail[REPO_NAME], "url": repo_detail[REPO_URL], "basearch": none2empty(repo_detail[REPO_BASEARCH]), "releasever": none2empty(repo_detail[REPO_RELEASEVER]), "product": repo_detail[REPO_PRODUCT], "revision": format_datetime(repo_detail[REPO_REVISION]), "cpes": [ self.cache.cpe_id2label[cpe_id] for cpe_id in cpe_ids ], "third_party": repo_detail[REPO_THIRD_PARTY] }) actual_page_size += len(repolist[label]) response = { 'repository_list': repolist, 'last_change': format_datetime(self.cache.dbchange['last_change']) } pagination_response['page_size'] = actual_page_size response.update(pagination_response) return response
def test_datetime_to_iso(self, date_param): """Test formatting datetime to ISO format.""" date = date_utils.format_datetime(date_param[1]) assert isinstance(date, str) assert RE_ISO.match(date) is not None