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

Changeset View

Standalone View

View Options

swh/storage/postgresql/db.py

# Copyright (C) 2015-2020 The Software Heritage developers # Copyright (C) 2015-2020 The Software Heritage developers
# See the AUTHORS file at the top-level directory of this distribution # See the AUTHORS file at the top-level directory of this distribution
# License: GNU General Public License version 3, or any later version # License: GNU General Public License version 3, or any later version
# See top-level LICENSE file for more information # See top-level LICENSE file for more information
import datetime import datetime
import logging import logging
import random import random
import select import select
from typing import Any, Dict, Iterable, List, Optional, Tuple from typing import Any, Dict, Iterable, List, Optional, Tuple
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.identifiers import ObjectType
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 = 168
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(
"SELECT swh_mktemp_dir_entry(%s)", (("directory_entry_%s" % entry_type),) "SELECT swh_mktemp_dir_entry(%s)", (("directory_entry_%s" % entry_type),)
) )
@stored_procedure("swh_mktemp_revision") @stored_procedure("swh_mktemp_revision")
def mktemp_revision(self, cur=None): def mktemp_revision(self, cur=None):
Show All 31 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(
self.mangle_query_key(k, "extid") for k in self.extid_cols
vlorentzUnsubmitted
Not Done
Inline Actions

why mangle_query_key?

vlorentz: why mangle_query_key?
)
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)
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
)
sql = """
SELECT %s
FROM (VALUES %%s) as t(sortkey, target, target_type)
LEFT JOIN extid USING (target, target_type)
ORDER BY sortkey
""" % (
vlorentzUnsubmitted
Done
Inline Actions

same

vlorentz: same
query_keys,
)
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