diff --git a/MANIFEST.in b/MANIFEST.in
index 807e2e9..9626d25 100644
--- a/MANIFEST.in
+++ b/MANIFEST.in
@@ -1,5 +1,7 @@
include Makefile
+include README.md
include requirements*.txt
include version.txt
-include README.md
+recursive-include sql *
+recursive-include swh/provenance/sql *
recursive-include swh py.typed
diff --git a/sql/upgrades/003.sql b/sql/upgrades/003.sql
new file mode 100644
index 0000000..95191b6
--- /dev/null
+++ b/sql/upgrades/003.sql
@@ -0,0 +1,13 @@
+-- SWH DB schema upgrade
+-- from_version: 2
+-- to_version: 3
+-- description: keep unique indices for origins and locations in a hash column
+
+
+insert into dbversion(version, release, description)
+ values(3, now(), 'Work In Progress');
+
+drop index if exists location_path_key;
+create unique index on location(digest(path::bytea, 'sha1'::text));
+
+drop index if exists origin_url_key;
diff --git a/swh/provenance/sql/10-superuser-init.sql b/swh/provenance/sql/10-superuser-init.sql
new file mode 100644
index 0000000..9e4f1e7
--- /dev/null
+++ b/swh/provenance/sql/10-superuser-init.sql
@@ -0,0 +1,2 @@
+-- require being Postgres super user
+create extension if not exists pgcrypto;
diff --git a/swh/provenance/sql/30-schema.sql b/swh/provenance/sql/30-schema.sql
index 980605f..038a2de 100644
--- a/swh/provenance/sql/30-schema.sql
+++ b/swh/provenance/sql/30-schema.sql
@@ -1,173 +1,173 @@
-- psql variables to get the current database flavor
select position('denormalized' in swh_get_dbflavor()::text) = 0 as dbflavor_norm \gset
select position('without-path' in swh_get_dbflavor()::text) = 0 as dbflavor_with_path \gset
create table dbversion
(
version int primary key,
release timestamptz,
description text
);
comment on table dbversion is 'Details of current db version';
comment on column dbversion.version is 'SQL schema version';
comment on column dbversion.release is 'Version deployment timestamp';
comment on column dbversion.description is 'Release description';
-- latest schema version
insert into dbversion(version, release, description)
- values(2, now(), 'Work In Progress');
+ values(3, now(), 'Work In Progress');
-- a Git object ID, i.e., a Git-style salted SHA1 checksum
create domain sha1_git as bytea check (length(value) = 20);
-- UNIX path (absolute, relative, individual path component, etc.)
create domain unix_path as bytea;
-- relation filter options for querying
create type rel_flt as enum (
'filter-src',
'filter-dst',
'no-filter'
);
comment on type rel_flt is 'Relation get filter types';
-- entity tables
create table content
(
id bigserial primary key, -- internal identifier of the content blob
sha1 sha1_git unique not null, -- intrinsic identifier of the content blob
date timestamptz not null -- timestamp of the revision where the blob appears early
);
comment on column content.id is 'Content internal identifier';
comment on column content.sha1 is 'Content intrinsic identifier';
comment on column content.date is 'Earliest timestamp for the content (first seen time)';
create table directory
(
id bigserial primary key, -- internal identifier of the directory appearing in an isochrone inner frontier
sha1 sha1_git unique not null, -- intrinsic identifier of the directory
date timestamptz not null, -- max timestamp among those of the directory children's
flat boolean not null default false -- flag acknowledging if the directory is flattenned in the model
);
comment on column directory.id is 'Directory internal identifier';
comment on column directory.sha1 is 'Directory intrinsic identifier';
comment on column directory.date is 'Latest timestamp for the content in the directory';
create table revision
(
id bigserial primary key, -- internal identifier of the revision
sha1 sha1_git unique not null, -- intrinsic identifier of the revision
date timestamptz, -- timestamp of the revision
origin bigint -- id of the preferred origin
-- foreign key (origin) references origin (id)
);
comment on column revision.id is 'Revision internal identifier';
comment on column revision.sha1 is 'Revision intrinsic identifier';
comment on column revision.date is 'Revision timestamp';
comment on column revision.origin is 'preferred origin for the revision';
create table location
(
id bigserial primary key, -- internal identifier of the location
- path unix_path unique not null -- path to the location
+ path unix_path -- path to the location
);
comment on column location.id is 'Location internal identifier';
comment on column location.path is 'Path to the location';
create table origin
(
id bigserial primary key, -- internal identifier of the origin
sha1 sha1_git unique not null, -- intrinsic identifier of the origin
- url text unique not null -- url of the origin
+ url text -- url of the origin
);
comment on column origin.id is 'Origin internal identifier';
comment on column origin.sha1 is 'Origin intrinsic identifier';
comment on column origin.url is 'URL of the origin';
-- relation tables
create table content_in_revision
(
content bigint not null, -- internal identifier of the content blob
\if :dbflavor_norm
revision bigint not null, -- internal identifier of the revision where the blob appears for the first time
location bigint -- location of the content relative to the revision's root directory
\else
revision bigint[], -- internal identifiers of the revisions where the blob appears for the first time
location bigint[] -- locations of the content relative to the revisions' root directory
\endif
-- foreign key (content) references content (id),
-- foreign key (revision) references revision (id),
-- foreign key (location) references location (id)
);
comment on column content_in_revision.content is 'Content internal identifier';
\if :dbflavor_norm
comment on column content_in_revision.revision is 'Revision internal identifier';
comment on column content_in_revision.location is 'Location of content in revision';
\else
comment on column content_in_revision.revision is 'Revision/location internal identifiers';
\endif
create table content_in_directory
(
content bigint not null, -- internal identifier of the content blob
\if :dbflavor_norm
directory bigint not null, -- internal identifier of the directory containing the blob
location bigint -- location of the content relative to its parent directory in the isochrone frontier
\else
directory bigint[], -- internal reference of the directories containing the blob
location bigint[] -- locations of the content relative to its parent directories in the isochrone frontier
\endif
-- foreign key (content) references content (id),
-- foreign key (directory) references directory (id),
-- foreign key (location) references location (id)
);
comment on column content_in_directory.content is 'Content internal identifier';
\if :dbflavor_norm
comment on column content_in_directory.directory is 'Directory internal identifier';
comment on column content_in_directory.location is 'Location of content in directory';
\else
comment on column content_in_directory.directory is 'Directory/location internal identifiers';
\endif
create table directory_in_revision
(
directory bigint not null, -- internal identifier of the directory appearing in the revision
\if :dbflavor_norm
revision bigint not null, -- internal identifier of the revision containing the directory
location bigint -- location of the directory relative to the revision's root directory
\else
revision bigint[], -- internal identifiers of the revisions containing the directory
location bigint[] -- locations of the directory relative to the revisions' root directory
\endif
-- foreign key (directory) references directory (id),
-- foreign key (revision) references revision (id),
-- foreign key (location) references location (id)
);
comment on column directory_in_revision.directory is 'Directory internal identifier';
\if :dbflavor_norm
comment on column directory_in_revision.revision is 'Revision internal identifier';
comment on column directory_in_revision.location is 'Location of content in revision';
\else
comment on column directory_in_revision.revision is 'Revision/location internal identifiers';
\endif
create table revision_in_origin
(
revision bigint not null, -- internal identifier of the revision poined by the origin
origin bigint not null -- internal identifier of the origin that points to the revision
-- foreign key (revision) references revision (id),
-- foreign key (origin) references origin (id)
);
comment on column revision_in_origin.revision is 'Revision internal identifier';
comment on column revision_in_origin.origin is 'Origin internal identifier';
create table revision_before_revision
(
prev bigserial not null, -- internal identifier of the source revision
next bigserial not null -- internal identifier of the destination revision
-- foreign key (prev) references revision (id),
-- foreign key (next) references revision (id)
);
comment on column revision_before_revision.prev is 'Source revision internal identifier';
comment on column revision_before_revision.next is 'Destination revision internal identifier';
diff --git a/swh/provenance/sql/60-indexes.sql b/swh/provenance/sql/60-indexes.sql
index 6141077..313fdbc 100644
--- a/swh/provenance/sql/60-indexes.sql
+++ b/swh/provenance/sql/60-indexes.sql
@@ -1,18 +1,19 @@
-- psql variables to get the current database flavor
select position('denormalized' in swh_get_dbflavor()::text) = 0 as dbflavor_norm \gset
-- create unique indexes (instead of pkey) because location might be null for
-- the without-path flavor
\if :dbflavor_norm
create unique index on content_in_revision(content, revision, location);
create unique index on directory_in_revision(directory, revision, location);
create unique index on content_in_directory(content, directory, location);
\else
create unique index on content_in_revision(content);
create unique index on directory_in_revision(directory);
create unique index on content_in_directory(content);
\endif
+create unique index on location(digest(path, 'sha1'));
alter table revision_in_origin add primary key (revision, origin);
alter table revision_before_revision add primary key (prev, next);
diff --git a/swh/provenance/tests/conftest.py b/swh/provenance/tests/conftest.py
index 32eb7d4..0edc3a1 100644
--- a/swh/provenance/tests/conftest.py
+++ b/swh/provenance/tests/conftest.py
@@ -1,168 +1,170 @@
# Copyright (C) 2021 The Software Heritage developers
# See the AUTHORS file at the top-level directory of this distribution
# License: GNU General Public License version 3, or any later version
# See top-level LICENSE file for more information
from datetime import datetime, timedelta, timezone
from os import path
from typing import Any, Dict, Generator, List
from _pytest.fixtures import SubRequest
import mongomock.database
import msgpack
import psycopg2.extensions
import pytest
from pytest_postgresql.factories import postgresql
from swh.journal.serializers import msgpack_ext_hook
from swh.model.model import BaseModel
from swh.provenance import get_provenance, get_provenance_storage
from swh.provenance.archive import ArchiveInterface
from swh.provenance.interface import ProvenanceInterface, ProvenanceStorageInterface
from swh.provenance.storage.archive import ArchiveStorage
from swh.storage.interface import StorageInterface
from swh.storage.replay import OBJECT_CONVERTERS, OBJECT_FIXERS, process_replay_objects
@pytest.fixture(
params=[
"with-path",
"without-path",
"with-path-denormalized",
"without-path-denormalized",
]
)
def provenance_postgresqldb(
request: SubRequest,
postgresql: psycopg2.extensions.connection,
) -> Dict[str, str]:
"""return a working and initialized provenance db"""
- from swh.core.cli.db import populate_database_for_package
+ from swh.core.cli.db import init_admin_extensions, populate_database_for_package
+ init_admin_extensions("swh.provenance", postgresql.dsn)
populate_database_for_package(
"swh.provenance", postgresql.dsn, flavor=request.param
)
return postgresql.get_dsn_parameters()
@pytest.fixture(params=["mongodb", "postgresql", "rabbitmq"])
def provenance_storage(
request: SubRequest,
provenance_postgresqldb: Dict[str, str],
mongodb: mongomock.database.Database,
) -> Generator[ProvenanceStorageInterface, None, None]:
"""Return a working and initialized ProvenanceStorageInterface object"""
if request.param == "mongodb":
mongodb_params = {
"host": mongodb.client.address[0],
"port": mongodb.client.address[1],
"dbname": mongodb.name,
}
with get_provenance_storage(
cls=request.param, db=mongodb_params, engine="mongomock"
) as storage:
yield storage
elif request.param == "rabbitmq":
from swh.provenance.api.server import ProvenanceStorageRabbitMQServer
rabbitmq = request.getfixturevalue("rabbitmq")
host = rabbitmq.args["host"]
port = rabbitmq.args["port"]
rabbitmq_params: Dict[str, Any] = {
"url": f"amqp://guest:guest@{host}:{port}/%2f",
"storage_config": {
"cls": "postgresql", # TODO: also test with underlying mongodb storage
"db": provenance_postgresqldb,
"raise_on_commit": True,
},
}
server = ProvenanceStorageRabbitMQServer(
url=rabbitmq_params["url"], storage_config=rabbitmq_params["storage_config"]
)
server.start()
with get_provenance_storage(cls=request.param, **rabbitmq_params) as storage:
yield storage
server.stop()
else:
# in test sessions, we DO want to raise any exception occurring at commit time
with get_provenance_storage(
cls=request.param, db=provenance_postgresqldb, raise_on_commit=True
) as storage:
yield storage
provenance_postgresql = postgresql("postgresql_proc", dbname="provenance_tests")
@pytest.fixture
def provenance(
provenance_postgresql: psycopg2.extensions.connection,
) -> Generator[ProvenanceInterface, None, None]:
"""Return a working and initialized ProvenanceInterface object"""
- from swh.core.cli.db import populate_database_for_package
+ from swh.core.cli.db import init_admin_extensions, populate_database_for_package
+ init_admin_extensions("swh.provenance", provenance_postgresql.dsn)
populate_database_for_package(
"swh.provenance", provenance_postgresql.dsn, flavor="with-path"
)
# in test sessions, we DO want to raise any exception occurring at commit time
with get_provenance(
cls="postgresql",
db=provenance_postgresql.get_dsn_parameters(),
raise_on_commit=True,
) as provenance:
yield provenance
@pytest.fixture
def archive(swh_storage: StorageInterface) -> ArchiveInterface:
"""Return an ArchiveStorage-based ArchiveInterface object"""
return ArchiveStorage(swh_storage)
def fill_storage(storage: StorageInterface, data: Dict[str, List[dict]]) -> None:
objects = {
objtype: [objs_from_dict(objtype, d) for d in dicts]
for objtype, dicts in data.items()
}
process_replay_objects(objects, storage=storage)
def get_datafile(fname: str) -> str:
return path.join(path.dirname(__file__), "data", fname)
# TODO: this should return Dict[str, List[BaseModel]] directly, but it requires
# refactoring several tests
def load_repo_data(repo: str) -> Dict[str, List[dict]]:
data: Dict[str, List[dict]] = {}
with open(get_datafile(f"{repo}.msgpack"), "rb") as fobj:
unpacker = msgpack.Unpacker(
fobj,
raw=False,
ext_hook=msgpack_ext_hook,
strict_map_key=False,
timestamp=3, # convert Timestamp in datetime objects (tz UTC)
)
for objtype, objd in unpacker:
data.setdefault(objtype, []).append(objd)
return data
def objs_from_dict(object_type: str, dict_repr: dict) -> BaseModel:
if object_type in OBJECT_FIXERS:
dict_repr = OBJECT_FIXERS[object_type](dict_repr)
obj = OBJECT_CONVERTERS[object_type](dict_repr)
return obj
# TODO: remove this function in favour of TimestampWithTimezone.to_datetime
# from swh.model.model
def ts2dt(ts: Dict[str, Any]) -> datetime:
timestamp = datetime.fromtimestamp(
ts["timestamp"]["seconds"], timezone(timedelta(minutes=ts["offset"]))
)
return timestamp.replace(microsecond=ts["timestamp"]["microseconds"])
diff --git a/swh/provenance/tests/test_cli.py b/swh/provenance/tests/test_cli.py
index 6ed98a2..660a78a 100644
--- a/swh/provenance/tests/test_cli.py
+++ b/swh/provenance/tests/test_cli.py
@@ -1,105 +1,108 @@
# Copyright (C) 2021 The Software Heritage developers
# See the AUTHORS file at the top-level directory of this distribution
# License: GNU General Public License version 3, or any later version
# See top-level LICENSE file for more information
from typing import Set
from _pytest.monkeypatch import MonkeyPatch
from click.testing import CliRunner
import psycopg2.extensions
import pytest
from swh.core.cli import swh as swhmain
import swh.core.cli.db # noqa ; ensure cli is loaded
+from swh.core.cli.db import init_admin_extensions
from swh.core.db import BaseDb
import swh.provenance.cli # noqa ; ensure cli is loaded
def test_cli_swh_db_help() -> None:
# swhmain.add_command(provenance_cli)
result = CliRunner().invoke(swhmain, ["provenance", "-h"])
assert result.exit_code == 0
assert "Commands:" in result.output
commands = result.output.split("Commands:")[1]
for command in (
"find-all",
"find-first",
"iter-frontiers",
"iter-origins",
"iter-revisions",
):
assert f" {command} " in commands
TABLES = {
"dbflavor",
"dbversion",
"content",
"content_in_revision",
"content_in_directory",
"directory",
"directory_in_revision",
"location",
"origin",
"revision",
"revision_before_revision",
"revision_in_origin",
}
@pytest.mark.parametrize(
"flavor, dbtables", (("with-path", TABLES | {"location"}), ("without-path", TABLES))
)
def test_cli_db_create_and_init_db_with_flavor(
monkeypatch: MonkeyPatch,
postgresql: psycopg2.extensions.connection,
flavor: str,
dbtables: Set[str],
) -> None:
"""Test that 'swh db init provenance' works with flavors
for both with-path and without-path flavors"""
dbname = f"{flavor}-db"
# DB creation using 'swh db create'
db_params = postgresql.get_dsn_parameters()
monkeypatch.setenv("PGHOST", db_params["host"])
monkeypatch.setenv("PGUSER", db_params["user"])
monkeypatch.setenv("PGPORT", db_params["port"])
result = CliRunner().invoke(swhmain, ["db", "create", "-d", dbname, "provenance"])
assert result.exit_code == 0, result.output
# DB init using 'swh db init'
result = CliRunner().invoke(
swhmain, ["db", "init", "-d", dbname, "--flavor", flavor, "provenance"]
)
assert result.exit_code == 0, result.output
assert f"(flavor {flavor})" in result.output
db_params["dbname"] = dbname
cnx = BaseDb.connect(**db_params).conn
# check the DB looks OK (check for db_flavor and expected tables)
with cnx.cursor() as cur:
cur.execute("select swh_get_dbflavor()")
assert cur.fetchone() == (flavor,)
cur.execute(
"select table_name from information_schema.tables "
"where table_schema = 'public' "
f"and table_catalog = '{dbname}'"
)
tables = set(x for (x,) in cur.fetchall())
assert tables == dbtables
def test_cli_init_db_default_flavor(postgresql: psycopg2.extensions.connection) -> None:
"Test that 'swh db init provenance' defaults to a with-path flavored DB"
+
dbname = postgresql.dsn
+ init_admin_extensions("swh.provenance", dbname)
result = CliRunner().invoke(swhmain, ["db", "init", "-d", dbname, "provenance"])
assert result.exit_code == 0, result.output
with postgresql.cursor() as cur:
cur.execute("select swh_get_dbflavor()")
assert cur.fetchone() == ("with-path",)