Page MenuHomeSoftware Heritage
Differential D4698 Diff 18681 swh/storage/postgresql/db.py

Changeset View

Standalone View

View Options

swh/storage/postgresql/db.py

Show All 11 Lines
from swh.core.db import BaseDb from swh.core.db import BaseDb
from swh.core.db.db_utils import execute_values_generator from swh.core.db.db_utils import execute_values_generator
from swh.core.db.db_utils import jsonize as _jsonize from swh.core.db.db_utils import jsonize as _jsonize
from swh.core.db.db_utils import stored_procedure from swh.core.db.db_utils import stored_procedure
from swh.model.model import SHA1_SIZE, OriginVisit, OriginVisitStatus from swh.model.model import SHA1_SIZE, OriginVisit, OriginVisitStatus
from swh.storage.interface import ListOrder from swh.storage.interface import ListOrder
logger = logging.getLogger(__name__) logger = logging.getLogger(__name__)
def jsonize(d): def jsonize(d):
return _jsonize(dict(d) if d is not None else None) return _jsonize(dict(d) if d is not None else None)
class Db(BaseDb): class Db(BaseDb):
vlorentzUnsubmitted
Not Done
Inline Actions

plz no, I removed it from swh.model.identifiers because we don't need it anymore.

OBJECT_TYPE_MAP[key] -> ObjectType(key)

vlorentz: plz no, I removed it from `swh.model.identifiers` because we don't need it anymore.
douarddaAuthorUnsubmitted
Done
Inline Actions

you mean ObjectType(key).name.lower() ?
It's hideous in any case, and that's exactly why I want to get rid of this naming/constant hell.

douardda: you mean `ObjectType(key).name.lower()` ? It's hideous in any case, and that's exactly why I…
vlorentzUnsubmitted
Not Done
Inline Actions

yes. but if you really want a map, let's add it back to swh-model, it doesn't belong in swh-storage

vlorentz: yes. but if you really want a map, let's add it back to swh-model, it doesn't belong in swh…
"""Proxy to the SWH DB, with wrappers around stored procedures """Proxy to the SWH DB, with wrappers around stored procedures
""" """
current_version = 167 current_version = 167
def mktemp_dir_entry(self, entry_type, cur=None): def mktemp_dir_entry(self, entry_type, cur=None):
self._cursor(cur).execute( self._cursor(cur).execute(
Show All 36 Linesclass Db(BaseDb):
@stored_procedure("swh_skipped_content_add") @stored_procedure("swh_skipped_content_add")
def skipped_content_add_from_temp(self, cur=None): def skipped_content_add_from_temp(self, cur=None):
pass pass
@stored_procedure("swh_revision_add") @stored_procedure("swh_revision_add")
def revision_add_from_temp(self, cur=None): def revision_add_from_temp(self, cur=None):
pass pass
@stored_procedure("swh_extid_add")
def extid_add_from_temp(self, cur=None):
pass
@stored_procedure("swh_release_add") @stored_procedure("swh_release_add")
def release_add_from_temp(self, cur=None): def release_add_from_temp(self, cur=None):
pass pass
def content_update_from_temp(self, keys_to_update, cur=None): def content_update_from_temp(self, keys_to_update, cur=None):
cur = self._cursor(cur) cur = self._cursor(cur)
cur.execute( cur.execute(
"""select swh_content_update(ARRAY[%s] :: text[])""" % keys_to_update """select swh_content_update(ARRAY[%s] :: text[])""" % keys_to_update
▲ Show 20 Lines • Show All 720 Lines • ▼ Show 20 Lines def revision_get_from_list(self, revisions, cur=None):
LEFT JOIN person author ON revision.author = author.id LEFT JOIN person author ON revision.author = author.id
LEFT JOIN person committer ON revision.committer = committer.id LEFT JOIN person committer ON revision.committer = committer.id
ORDER BY sortkey ORDER BY sortkey
""" """
% query_keys, % query_keys,
((sortkey, id) for sortkey, id in enumerate(revisions)), ((sortkey, id) for sortkey, id in enumerate(revisions)),
) )
extid_cols = ["extid", "extid_type", "target", "target_type"]
def extid_get_from_extid_list(self, extid_type, ids, cur=None):
cur = self._cursor(cur)
query_keys = ", ".join(
vlorentzUnsubmitted
Not Done
Inline Actions

why mangle_query_key?

vlorentz: why mangle_query_key?
self.mangle_query_key(k, "extid") for k in self.extid_cols
)
sql = """
SELECT %s
FROM (VALUES %%s) as t(sortkey, extid, extid_type)
LEFT JOIN extid USING (extid, extid_type)
ORDER BY sortkey
vlorentzUnsubmitted
Not Done
Inline Actions

Doesn't this load *all* the extids of a given VCS in RAM? This could get quite big for Mercurial...

You could join with the extid table directly.

vlorentz: Doesn't this load *all* the extids of a given VCS in RAM? This could get quite big for…
douarddaAuthorUnsubmitted
Done
Inline Actions

yeah I was pretty sure while writing this that it was a naive approach... as you can see, my SQL skills are pretty narrow...

douardda: yeah I was pretty sure while writing this that it was a naive approach... as you can see, my…
""" % (
query_keys,
)
yield from execute_values_generator(
cur,
sql,
(((sortkey, extid, extid_type) for sortkey, extid in enumerate(ids))),
)
olasdUnsubmitted
Done
Inline Actions
self.mangle_query_key(k, "filtered_extid") for k in self.extid_cols
)
sql = """
- WITH filtered_extid AS (
- SELECT *
- FROM extid
- WHERE extid_type = '%s'
- )
- SELECT %s FROM (VALUES %%s) as t(sortkey, id)
- LEFT JOIN filtered_extid ON t.id = filtered_extid.extid
+ SELECT %s FROM (VALUES %%s) as t(sortkey, extid_type, extid)
+ LEFT JOIN extid USING (extid_type, extid)
ORDER BY sortkey
""" % (
- extid_type,
query_keys,
)
yield from execute_values_generator(
- cur, sql, (((sortkey, id) for sortkey, id in enumerate(ids))),
+ cur, sql, (((sortkey, extid_type, extid) for sortkey, extid in enumerate(ids))),
)
def extid_get_from_swhid_list(self, target_type, ids, cur=None):

I haven't tested this but I think it should work.

olasd: I haven't tested this but I think it should work.
vlorentzUnsubmitted
Done
Inline Actions

alternatively, LEFT JOIN extid USING (extid) WHERE extid_type = '%s'. both are equivalent.

vlorentz: alternatively, `LEFT JOIN extid USING (extid) WHERE extid_type = '%s'`. both are equivalent.
douarddaAuthorUnsubmitted
Done
Inline Actions

that's what I tried but it breaks my tests (because [] != [None, None, None, None])

douardda: that's what I tried but it breaks my tests (because [] != [None, None, None, None])
vlorentzUnsubmitted
Done
Inline Actions

right. you need an inner join.

(as a good rule of thumb, always use inner joins in SQL, one rarely ever needs the other types of joins)

vlorentz: right. you need an `inner join`. (as a good rule of thumb, always use inner joins in SQL, one…
douarddaAuthorUnsubmitted
Done
Inline Actions

I finally have it working (with a left join). My main problem was to figure how to make it happy with the USING(target_type) (in extid_get_from_swhid_list)...

douardda: I finally have it working (with a left join). My main problem was to figure how to make it…
def extid_get_from_swhid_list(self, target_type, ids, cur=None):
cur = self._cursor(cur)
query_keys = ", ".join(
self.mangle_query_key(k, "extid") for k in self.extid_cols
)
sql = """
SELECT %s
FROM (VALUES %%s) as t(sortkey, target, target_type)
LEFT JOIN extid USING (target, target_type)
ORDER BY sortkey
""" % (
query_keys,
vlorentzUnsubmitted
Done
Inline Actions

same

vlorentz: same
)
yield from execute_values_generator(
cur,
sql,
(((sortkey, target, target_type) for sortkey, target in enumerate(ids))),
template=b"(%s,%s,%s::object_type)",
)
def revision_log(self, root_revisions, limit=None, cur=None): def revision_log(self, root_revisions, limit=None, cur=None):
cur = self._cursor(cur) cur = self._cursor(cur)
query = """SELECT %s query = """SELECT %s
FROM swh_revision_log(%%s, %%s) FROM swh_revision_log(%%s, %%s)
""" % ", ".join( """ % ", ".join(
self.revision_get_cols self.revision_get_cols
) )
▲ Show 20 Lines • Show All 552 Lines • Show Last 20 Lines
About · Developer information · Legal