def _missing_from_list( self, table: str, data: Iterable[Dict], hash_keys: List[str], cur=None ): """Read from table the data with hash_keys that are missing. Args: table: Table name (e.g content_mimetype, content_language, etc...) data: Dict of data to read from hash_keys: List of keys to read in the data dict. Yields: The data which is missing from the db. """ cur = self._cursor(cur) keys = ", ".join(hash_keys) equality = " AND ".join(("t.%s = c.%s" % (key, key)) for key in hash_keys) yield from execute_values_generator( cur, """ select %s from (values %%s) as t(%s) where not exists ( select 1 from %s c where %s ) """ % (keys, keys, table, equality), (tuple(m[k] for k in hash_keys) for m in data), )
def extid_get_from_extid_list(self, extid_type: str, ids: List[bytes], version: Optional[int] = None, cur=None): cur = self._cursor(cur) query_keys = ", ".join( self.mangle_query_key(k, "extid") for k in self.extid_cols) filter_query = "" if version is not None: filter_query = cur.mogrify(f"WHERE extid_version={version}", (version, )).decode() sql = f""" SELECT {query_keys} FROM (VALUES %s) as t(sortkey, extid, extid_type) LEFT JOIN extid USING (extid, extid_type) {filter_query} ORDER BY sortkey """ yield from execute_values_generator( cur, sql, (((sortkey, extid, extid_type) for sortkey, extid in enumerate(ids))), )
def raw_extrinsic_metadata_get_by_ids(self, ids: List[Sha1Git], cur=None): cur = self._cursor(cur) yield from execute_values_generator( cur, self._raw_extrinsic_metadata_select_query + "INNER JOIN (VALUES %s) AS t(id) ON t.id = raw_extrinsic_metadata.id", [(id_, ) for id_ in ids], )
def origin_get_by_url(self, origins, cur=None): """Retrieve origin `(type, url)` from urls if found.""" cur = self._cursor(cur) query = """SELECT %s FROM (VALUES %%s) as t(url) LEFT JOIN origin ON t.url = origin.url """ % ",".join("origin." + col for col in self.origin_cols) yield from execute_values_generator(cur, query, ((url, ) for url in origins))
def origin_get_by_sha1(self, sha1s, cur=None): """Retrieve origin urls from sha1s if found.""" cur = self._cursor(cur) query = """SELECT %s FROM (VALUES %%s) as t(sha1) LEFT JOIN origin ON t.sha1 = digest(origin.url, 'sha1') """ % ",".join("origin." + col for col in self.origin_cols) yield from execute_values_generator(cur, query, ((sha1, ) for sha1 in sha1s))
def origin_id_get_by_url(self, origins, cur=None): """Retrieve origin `(type, url)` from urls if found.""" cur = self._cursor(cur) query = """SELECT id FROM (VALUES %s) as t(url) LEFT JOIN origin ON t.url = origin.url """ for row in execute_values_generator(cur, query, ((url, ) for url in origins)): yield row[0]
def directory_missing_from_list(self, directories, cur=None): cur = self._cursor(cur) yield from execute_values_generator( cur, """ SELECT id FROM (VALUES %s) as t(id) WHERE NOT EXISTS ( SELECT 1 FROM directory d WHERE d.id = t.id ) """, ((id, ) for id in directories), )
def snapshot_missing_from_list(self, snapshots, cur=None): cur = self._cursor(cur) yield from execute_values_generator( cur, """ SELECT id FROM (VALUES %s) as t(id) WHERE NOT EXISTS ( SELECT 1 FROM snapshot d WHERE d.id = t.id ) """, ((id, ) for id in snapshots), )
def content_missing_per_sha1_git(self, contents, cur=None): cur = self._cursor(cur) yield from execute_values_generator( cur, """ SELECT t.sha1_git FROM (VALUES %s) AS t(sha1_git) WHERE NOT EXISTS ( SELECT 1 FROM content c WHERE c.sha1_git = t.sha1_git )""", ((sha1, ) for sha1 in contents), )
def release_missing_from_list(self, releases, cur=None): cur = self._cursor(cur) yield from execute_values_generator( cur, """ SELECT id FROM (VALUES %s) as t(id) WHERE NOT EXISTS ( SELECT 1 FROM release r WHERE r.id = t.id ) """, ((id, ) for id in releases), )
def directory_get_raw_manifest( self, directory_ids: List[Sha1Git], cur=None) -> Iterable[Tuple[Sha1Git, bytes]]: cur = self._cursor(cur) yield from execute_values_generator( cur, """ SELECT t.id, raw_manifest FROM (VALUES %s) as t(id) INNER JOIN directory ON (t.id=directory.id) """, ((id_, ) for id_ in directory_ids), )
def content_get_metadata_from_hashes(self, hashes: List[bytes], algo: str, cur=None): cur = self._cursor(cur) assert algo in DEFAULT_ALGORITHMS query = f""" select {", ".join(self.content_get_metadata_keys)} from (values %s) as t (hash) inner join content on (content.{algo}=hash) """ yield from execute_values_generator( cur, query, ((hash_, ) for hash_ in hashes), )
def content_ctags_get_from_list(self, ids, cur=None): cur = self._cursor(cur) keys = map(self._convert_key, self.content_ctags_cols) yield from execute_values_generator( cur, """ select %s from (values %%s) as t(id) inner join content_ctags c on c.id=t.id inner join indexer_configuration i on c.indexer_configuration_id=i.id order by line """ % ", ".join(keys), ((_id,) for _id in ids), )
def content_fossology_license_get_from_list(self, ids, cur=None): """Retrieve licenses per id. """ cur = self._cursor(cur) keys = map(self._convert_key, self.content_fossology_license_cols) yield from execute_values_generator( cur, """ select %s from (values %%s) as t(id) inner join content_fossology_license c on t.id=c.id inner join indexer_configuration i on i.id=c.indexer_configuration_id """ % ", ".join(keys), ((_id,) for _id in ids), )
def release_get_from_list(self, releases, ignore_displayname=False, cur=None): cur = self._cursor(cur) query_keys = ", ".join( self.mangle_query_key(k, "release", ignore_displayname) for k in self.release_get_cols) yield from execute_values_generator( cur, """ SELECT %s FROM (VALUES %%s) as t(sortkey, id) LEFT JOIN release ON t.id = release.id LEFT JOIN person author ON release.author = author.id ORDER BY sortkey """ % query_keys, ((sortkey, id) for sortkey, id in enumerate(releases)), )
def _get_from_list(self, table, ids, cols, cur=None, id_col="id"): """Fetches entries from the `table` such that their `id` field (or whatever is given to `id_col`) is in `ids`. Returns the columns `cols`. The `cur` parameter is used to connect to the database. """ cur = self._cursor(cur) keys = map(self._convert_key, cols) query = """ select {keys} from (values %s) as t(id) inner join {table} c on c.{id_col}=t.id inner join indexer_configuration i on c.indexer_configuration_id=i.id; """.format( keys=", ".join(keys), id_col=id_col, table=table ) yield from execute_values_generator(cur, query, ((_id,) for _id in ids))
def object_find_by_sha1_git(self, ids, cur=None): cur = self._cursor(cur) yield from execute_values_generator( cur, """ WITH t (sha1_git) AS (VALUES %s), known_objects as (( select id as sha1_git, 'release'::object_type as type, object_id from release r where exists (select 1 from t where t.sha1_git = r.id) ) union all ( select id as sha1_git, 'revision'::object_type as type, object_id from revision r where exists (select 1 from t where t.sha1_git = r.id) ) union all ( select id as sha1_git, 'directory'::object_type as type, object_id from directory d where exists (select 1 from t where t.sha1_git = d.id) ) union all ( select sha1_git as sha1_git, 'content'::object_type as type, object_id from content c where exists (select 1 from t where t.sha1_git = c.sha1_git) )) select t.sha1_git as sha1_git, k.type from t left join known_objects k on t.sha1_git = k.sha1_git """, ((id, ) for id in ids), )
def content_missing_from_list(self, contents, cur=None): cur = self._cursor(cur) keys = ", ".join(self.content_hash_keys) equality = " AND ".join( ("t.%s = c.%s" % (key, key)) for key in self.content_hash_keys) yield from execute_values_generator( cur, """ SELECT %s FROM (VALUES %%s) as t(%s) WHERE NOT EXISTS ( SELECT 1 FROM content c WHERE %s ) """ % (keys, keys, equality), (tuple(c[key] for key in self.content_hash_keys) for c in contents), )
def extid_get_from_swhid_list( self, target_type: str, ids: List[bytes], extid_version: Optional[int] = None, extid_type: Optional[str] = None, cur=None, ): cur = self._cursor(cur) target_type = ObjectType( target_type).name.lower() # aka "rev" -> "revision", ... query_keys = ", ".join( self.mangle_query_key(k, "extid") for k in self.extid_cols) filter_query = "" if extid_version is not None and extid_type is not None: filter_query = cur.mogrify( "WHERE extid_version=%s AND extid_type=%s", ( extid_version, extid_type, ), ).decode() sql = f""" SELECT {query_keys} FROM (VALUES %s) as t(sortkey, target, target_type) LEFT JOIN extid USING (target, target_type) {filter_query} ORDER BY sortkey """ yield from execute_values_generator( cur, sql, (((sortkey, target, target_type) for sortkey, target in enumerate(ids))), template=b"(%s,%s,%s::object_type)", )