Page MenuHomeSoftware Heritage
Differential D6090

Refactor `ProvenanceStoragePostgreSql`
ClosedPublic
Actions

Authored by aeviso on Aug 16 2021, 11:27 AM.

Details

Reviewers
olasd
Group Reviewers
Reviewers
Commits
rDPROVec1827e6eb00: Improve denormalized PostgreSQL schema to properly check for duplicates
rDPROV9076e8a1dd34: Add SQL enum for relation get filter options
rDPROVb523dfd8c1f8: Refactor `relation_add` prepearing to move SQL queries into functions
rDPROVe529372cf218: Move PosgreSQL's backend method `relation_add` to SQL function
rDPROVacd76e442fbe: Move PosgreSQL's backend method `relation_get` to SQL function
Summary

Move PosgreSQL's backend method relation_get to SQL function

Refactor relation_add prepearing to move SQL queries into functions

The four flavor's cases are made explicit to simplify the reading and the
definition of future SQL functions.

Move PosgreSQL's backend method relation_add to SQL function

Improve denormalized PostgreSQL schema to properly check for duplicates

Depends on D6084.

Diff Detail

Event Timeline

aeviso created this revision.Aug 16 2021, 11:27 AM
Herald added a reviewer: Reviewers. · View Herald TranscriptAug 16 2021, 11:27 AM
aeviso retitled this revision from Refactor `ProvenanceStoragePostgreSql` Refactor to improve readability and fix an issue when inserting duplicated in the denormalize flavor. Relation associated methods (add/get) are not SQL functions with specific implementations for each flavor. to Refactor `ProvenanceStoragePostgreSql`.Aug 16 2021, 11:27 AM
swh-public-ci added a comment.Aug 16 2021, 11:29 AM

Build is green

Patch application report for D6090 (id=22052)

Could not rebase; Attempt merge onto b0951e2e21...

Updating b0951e2..2a55cd5
Fast-forward
 docs/index.rst                                     |   2 +-
 requirements-test.txt                              |   1 +
 requirements.txt                                   |   1 -
 swh/provenance/__init__.py                         |   6 +-
 swh/provenance/api/server.py                       |   4 +-
 swh/provenance/cli.py                              |   2 +-
 .../postgresql/{provenancedb.py => provenance.py}  | 157 ++---
 swh/provenance/sql/30-schema.sql                   |  55 +-
 swh/provenance/sql/40-funcs.sql                    | 724 ++++++++++++++++-----
 swh/provenance/tests/conftest.py                   |   8 +-
 swh/provenance/tests/test_conftest.py              |   3 +-
 swh/provenance/tests/test_provenance_db.py         |   4 +-
 .../tests/test_revision_content_layer.py           |   6 +-
 13 files changed, 640 insertions(+), 333 deletions(-)
 rename swh/provenance/postgresql/{provenancedb.py => provenance.py} (64%)
Changes applied before test
commit 2a55cd54bac17448c720eb5699e308c8cad08b27
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Mon Aug 16 11:10:06 2021 +0200

    Improve denormalized PostgreSQL schema to properly check for duplicates

commit 3ef3fe25377198b74d10b7cc0e9ff9e6a86229ee
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Sun Aug 15 12:55:43 2021 +0200

    Move PosgreSQL's backend method `relation_add` to SQL function

commit 72bd4f4b2477acdeffc553d282ead1257fefc56c
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Fri Aug 13 15:35:10 2021 +0200

    Refactor `relation_add` prepearing to move SQL queries into functions
    
    The four flavor's cases are made explicit to simplify the reading and the
    definition of future SQL functions.

commit 64a963bd8f6fe6e5a4eba546bd100fc266f46573
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Wed Aug 11 21:48:24 2021 +0200

    Move PosgreSQL's backend method `relation_get` to SQL function

commit 5ea920ac03a141a956ab6f2c2ed1845281d8a368
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Fri Aug 13 10:45:19 2021 +0200

    Fix module requirements
    
    `types-Werkzeug` is only required for testing.

commit b4baa4a5dfb535936b7450fed7873003dda9c6ad
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Fri Aug 13 10:35:11 2021 +0200

    Fix bugs in ProvenanceStoragePostgreSql class
    
    `location_get` was decoding paths to string instead of returning bytes directly as the interface requires.
    `revision_get` was exposing internal origins id instead of joining with the `origin` table to retrive their sha1s.

commit 73bd38eeb8041044f9934e74b46579700da4aa2c
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Tue Aug 10 17:06:11 2021 +0200

    Rename PostgreSQL backend and code styling

See https://jenkins.softwareheritage.org/job/DPROV/job/tests-on-diff/295/ for more details.

Harbormaster completed remote builds in B23008: Diff 22052.Aug 16 2021, 11:29 AM
aeviso requested review of this revision.Aug 16 2021, 11:29 AM
aeviso updated this revision to Diff 22054.Aug 16 2021, 11:42 AM

rebase

swh-public-ci added a comment.Aug 16 2021, 11:47 AM

Build is green

Patch application report for D6090 (id=22054)

Could not rebase; Attempt merge onto b0951e2e21...

Updating b0951e2..b01939b
Fast-forward
 docs/index.rst                                     |   2 +-
 requirements-test.txt                              |   1 +
 requirements.txt                                   |   1 -
 swh/provenance/__init__.py                         |  14 +-
 swh/provenance/api/server.py                       |   4 +-
 swh/provenance/cli.py                              |   2 +-
 .../postgresql/{provenancedb.py => provenance.py}  | 157 ++---
 swh/provenance/sql/30-schema.sql                   |  55 +-
 swh/provenance/sql/40-funcs.sql                    | 724 ++++++++++++++++-----
 swh/provenance/tests/conftest.py                   |   8 +-
 swh/provenance/tests/test_conftest.py              |   3 +-
 swh/provenance/tests/test_provenance_db.py         |   4 +-
 .../tests/test_revision_content_layer.py           |   6 +-
 13 files changed, 648 insertions(+), 333 deletions(-)
 rename swh/provenance/postgresql/{provenancedb.py => provenance.py} (64%)
Changes applied before test
commit b01939bc7df042c914c96529a74233bc6f120770
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Mon Aug 16 11:10:06 2021 +0200

    Improve denormalized PostgreSQL schema to properly check for duplicates

commit e9843bfb3c37e2d54facf3f7fb6fa450fed38d91
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Sun Aug 15 12:55:43 2021 +0200

    Move PosgreSQL's backend method `relation_add` to SQL function

commit e697f8480846848f58f33946961d6ab3b1977c97
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Fri Aug 13 15:35:10 2021 +0200

    Refactor `relation_add` prepearing to move SQL queries into functions
    
    The four flavor's cases are made explicit to simplify the reading and the
    definition of future SQL functions.

commit eb8434515c7806a51372f6c4bfb25e2bf314dac6
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Wed Aug 11 21:48:24 2021 +0200

    Move PosgreSQL's backend method `relation_get` to SQL function

commit 2b62b1635651aa76211ce2d81b7d4e5ba0da63e6
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Fri Aug 13 10:45:19 2021 +0200

    Fix module requirements
    
    `types-Werkzeug` is only required for testing.

commit 68e1c8ffddf6264923b28bef9f6836c01a5d8a12
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Fri Aug 13 10:35:11 2021 +0200

    Fix bugs in ProvenanceStoragePostgreSql class
    
    `location_get` was decoding paths to string instead of returning bytes directly as the interface requires.
    `revision_get` was exposing internal origins id instead of joining with the `origin` table to retrive their sha1s.

commit 2086effea8427c256e472035241b1d8d7e121bd9
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Tue Aug 10 17:06:11 2021 +0200

    Rename PostgreSQL backend and code styling

See https://jenkins.softwareheritage.org/job/DPROV/job/tests-on-diff/297/ for more details.

Harbormaster completed remote builds in B23010: Diff 22054.Aug 16 2021, 11:47 AM
aeviso updated this revision to Diff 22111.Aug 18 2021, 11:58 AM

rebase

Harbormaster completed remote builds in B23066: Diff 22111.Aug 18 2021, 12:00 PM
swh-public-ci added a comment.Aug 18 2021, 12:00 PM

Build is green

Patch application report for D6090 (id=22111)

Rebasing onto e9206efef3...

Current branch diff-target is up to date.
Changes applied before test
commit 7a5c868e223ab6f08398427c7b0c05bc400a51ad
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Mon Aug 16 11:10:06 2021 +0200

    Improve denormalized PostgreSQL schema to properly check for duplicates

commit 56ea73ec2ca3433593ae7e753c494fa3b8e1a6b1
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Sun Aug 15 12:55:43 2021 +0200

    Move PosgreSQL's backend method `relation_add` to SQL function

commit 26493d5e5bb085c5f54002354770bf07dced40c5
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Fri Aug 13 15:35:10 2021 +0200

    Refactor `relation_add` prepearing to move SQL queries into functions
    
    The four flavor's cases are made explicit to simplify the reading and the
    definition of future SQL functions.

commit f9809ed858b29af749fa03dcf99baa4a1e1bc836
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Wed Aug 18 11:37:30 2021 +0200

    Move PosgreSQL's backend method `relation_get` to SQL function

See https://jenkins.softwareheritage.org/job/DPROV/job/tests-on-diff/299/ for more details.

aeviso updated this revision to Diff 22220.Aug 26 2021, 11:05 AM

rebase

aeviso updated this revision to Diff 22221.Aug 26 2021, 11:06 AM
  • Add SQL enum for relation get filter options
swh-public-ci added a comment.Aug 26 2021, 11:07 AM

Build is green

Patch application report for D6090 (id=22220)

Rebasing onto 3e009a2f77...

Current branch diff-target is up to date.
Changes applied before test
commit da8bed6355caf242a5dbf1d4d8c4326143a7150d
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Mon Aug 16 11:10:06 2021 +0200

    Improve denormalized PostgreSQL schema to properly check for duplicates

commit 14a6dc40390d327103d14a2b364540333a8a9360
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Sun Aug 15 12:55:43 2021 +0200

    Move PosgreSQL's backend method `relation_add` to SQL function

commit 890b02308df29727f415b7885cf30a4efdf074b5
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Fri Aug 13 15:35:10 2021 +0200

    Refactor `relation_add` prepearing to move SQL queries into functions
    
    The four flavor's cases are made explicit to simplify the reading and the
    definition of future SQL functions.

commit e7369c7f61f383e7d41b2ed25f4e69fd542a400a
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Wed Aug 18 11:37:30 2021 +0200

    Move PosgreSQL's backend method `relation_get` to SQL function

See https://jenkins.softwareheritage.org/job/DPROV/job/tests-on-diff/311/ for more details.

Harbormaster completed remote builds in B23178: Diff 22220.Aug 26 2021, 11:07 AM
swh-public-ci added a comment.Aug 26 2021, 11:10 AM

Build is green

Patch application report for D6090 (id=22221)

Rebasing onto 3e009a2f77...

Current branch diff-target is up to date.
Changes applied before test
commit c19cf531d7058a38dee7509e001a3a488d36d0c7
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Thu Aug 19 14:40:48 2021 +0200

    Add SQL enum for relation get filter options

commit da8bed6355caf242a5dbf1d4d8c4326143a7150d
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Mon Aug 16 11:10:06 2021 +0200

    Improve denormalized PostgreSQL schema to properly check for duplicates

commit 14a6dc40390d327103d14a2b364540333a8a9360
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Sun Aug 15 12:55:43 2021 +0200

    Move PosgreSQL's backend method `relation_add` to SQL function

commit 890b02308df29727f415b7885cf30a4efdf074b5
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Fri Aug 13 15:35:10 2021 +0200

    Refactor `relation_add` prepearing to move SQL queries into functions
    
    The four flavor's cases are made explicit to simplify the reading and the
    definition of future SQL functions.

commit e7369c7f61f383e7d41b2ed25f4e69fd542a400a
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Wed Aug 18 11:37:30 2021 +0200

    Move PosgreSQL's backend method `relation_get` to SQL function

See https://jenkins.softwareheritage.org/job/DPROV/job/tests-on-diff/312/ for more details.

Harbormaster completed remote builds in B23179: Diff 22221.Aug 26 2021, 11:10 AM
aeviso updated this revision to Diff 22229.Aug 26 2021, 11:54 AM

rebase

swh-public-ci added a comment.Aug 26 2021, 11:57 AM

Build is green

Patch application report for D6090 (id=22229)

Rebasing onto 3e009a2f77...

Current branch diff-target is up to date.
Changes applied before test
commit b8549affec1e448e3ad40b840af51f9e6380ae63
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Thu Aug 19 14:40:48 2021 +0200

    Add SQL enum for relation get filter options

commit 0c9605f1efe629520c7dc8566b155a5e54489b71
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Mon Aug 16 11:10:06 2021 +0200

    Improve denormalized PostgreSQL schema to properly check for duplicates

commit 89e68ac8ee637d8c129275e29f4ef11002bd7f9a
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Thu Aug 26 11:35:48 2021 +0200

    Move PosgreSQL's backend method `relation_add` to SQL function

commit d8ca7ddf5f76ee729f3224a1274da1ffb3518a67
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Fri Aug 13 15:35:10 2021 +0200

    Refactor `relation_add` prepearing to move SQL queries into functions
    
    The four flavor's cases are made explicit to simplify the reading and the
    definition of future SQL functions.

commit 667e0a4bcfc20e17c488ff50488859a8e9f9193d
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Wed Aug 18 11:37:30 2021 +0200

    Move PosgreSQL's backend method `relation_get` to SQL function

See https://jenkins.softwareheritage.org/job/DPROV/job/tests-on-diff/313/ for more details.

Harbormaster completed remote builds in B23187: Diff 22229.Aug 26 2021, 11:57 AM
aeviso updated this revision to Diff 22237.Aug 26 2021, 12:28 PM

Re-enable provenance storage tests that were disabled by mistake

swh-public-ci added a comment.Aug 26 2021, 12:31 PM

Build is green

Patch application report for D6090 (id=22237)

Rebasing onto 3e009a2f77...

Current branch diff-target is up to date.
Changes applied before test
commit 9076e8a1dd3468dc400007824be4ca51c4f5c131
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Thu Aug 19 14:40:48 2021 +0200

    Add SQL enum for relation get filter options

commit ec1827e6eb00e24ded1a043a48b5610949e5251c
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Mon Aug 16 11:10:06 2021 +0200

    Improve denormalized PostgreSQL schema to properly check for duplicates

commit e529372cf218ff2cfb9b88950a6973e2fac6b6bf
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Thu Aug 26 11:35:48 2021 +0200

    Move PosgreSQL's backend method `relation_add` to SQL function

commit b523dfd8c1f803e8dbe9d2414c1f3cd08b0cf2ce
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Fri Aug 13 15:35:10 2021 +0200

    Refactor `relation_add` prepearing to move SQL queries into functions
    
    The four flavor's cases are made explicit to simplify the reading and the
    definition of future SQL functions.

commit acd76e442fbefb5b3055e06daf8a87afcb53ee48
Author: Andres Ezequiel Viso <aeviso@softwareheritage.org>
Date:   Wed Aug 18 11:37:30 2021 +0200

    Move PosgreSQL's backend method `relation_get` to SQL function

See https://jenkins.softwareheritage.org/job/DPROV/job/tests-on-diff/314/ for more details.

Harbormaster completed remote builds in B23195: Diff 22237.Aug 26 2021, 12:31 PM
aeviso added a child revision: D6165: Add new RabbitMQ-based client/server API.Aug 31 2021, 2:03 PM
olasd accepted this revision.Sep 7 2021, 3:11 PM
This revision is now accepted and ready to land.Sep 7 2021, 3:11 PM
olasd added a child revision: D6204: Use a temporary table to add relations.Sep 7 2021, 3:12 PM
Closed by commit rDPROVacd76e442fbe: Move PosgreSQL's backend method `relation_get` to SQL function (authored by aeviso). · Explain WhySep 7 2021, 3:14 PM
This revision was automatically updated to reflect the committed changes.
aeviso added a commit: rDPROVacd76e442fbe: Move PosgreSQL's backend method `relation_get` to SQL function.
aeviso added a commit: rDPROVb523dfd8c1f8: Refactor `relation_add` prepearing to move SQL queries into functions.
aeviso added a commit: rDPROVe529372cf218: Move PosgreSQL's backend method `relation_add` to SQL function.
aeviso added a commit: rDPROVec1827e6eb00: Improve denormalized PostgreSQL schema to properly check for duplicates.
aeviso added a commit: rDPROV9076e8a1dd34: Add SQL enum for relation get filter options.
aeviso removed a child revision: D6165: Add new RabbitMQ-based client/server API.Sep 7 2021, 3:54 PM
aeviso added a child revision: D6165: Add new RabbitMQ-based client/server API.Sep 7 2021, 4:53 PM
aeviso removed a child revision: D6165: Add new RabbitMQ-based client/server API.Sep 7 2021, 4:54 PM
aeviso added a child revision: D6165: Add new RabbitMQ-based client/server API.Sep 14 2021, 2:53 PM
aeviso removed a child revision: D6165: Add new RabbitMQ-based client/server API.Sep 14 2021, 2:55 PM
aeviso added a child revision: D6165: Add new RabbitMQ-based client/server API.Sep 14 2021, 3:09 PM
aeviso removed a child revision: D6165: Add new RabbitMQ-based client/server API.Sep 14 2021, 3:10 PM
aeviso added a child revision: D6165: Add new RabbitMQ-based client/server API.Sep 14 2021, 3:23 PM
aeviso removed a child revision: D6165: Add new RabbitMQ-based client/server API.Sep 14 2021, 3:41 PM

Revision Contents
Changeset List

PathSizeCoverage (All)Coverage (Touched)
swh/
provenance/
postgresql/
144 lines89%
Loading...
sql/
55 lines--
681 lines--

Diff 22221

View Options

swh/provenance/postgresql/provenance.py

Loading...
View Options

swh/provenance/sql/30-schema.sql

Loading...
View Options

swh/provenance/sql/40-funcs.sql

Loading...
About · Developer information · Legal