Page MenuHomeSoftware Heritage

Add a 'swh db upgrade' command
ClosedPublic

Authored by douardda on Feb 2 2022, 1:53 PM.

Details

Summary

this new command allows to handle the database migration from the
current version to the latest version advertized but the backend
datastore.

Thie feature is only working for "new style" swh.core.db based
datastores.

Depends on D7063
Related to T3894

Unit TestsFailed

TimeTest
423 msJenkins > .tox.py3-core-db-server-slow-cover.lib.python3.7.site-packages.swh.core.db.tests.test_cli::test_cli_swh_db_create_and_init_db_new_api
cli_runner = <click.testing.CliRunner object at 0x7f5a7d0a13c8> postgresql = <connection object at 0x7f5a7f09ce08; dsn: 'user=postgres password=xxx dbname=tests host=127.0.0.1 port=13987 options=''', closed: 0> mock_package_sql = <function mock_package_sql.<locals>.get_sql_for_package_mock at 0x7f5a7cfd1d90>
476 msJenkins > .tox.py3-core-db-server-slow-cover.lib.python3.7.site-packages.swh.core.db.tests.test_cli::test_cli_swh_db_upgrade_new_api
cli_runner = <click.testing.CliRunner object at 0x7f5a7d0d6748> postgresql = <connection object at 0x7f5a7f09c408; dsn: 'user=postgres password=xxx dbname=tests host=127.0.0.1 port=13987 options=''', closed: 0> mock_package_sql = <function mock_package_sql.<locals>.get_sql_for_package_mock at 0x7f5a7d055e18>
428 msJenkins > .tox.py3-core-db-server-slow-cover.lib.python3.7.site-packages.swh.core.db.tests.test_db_utils::test_db_utils_upgrade[test.cli_new]
cli_runner = <click.testing.CliRunner object at 0x7f5a7c079710> postgresql = <connection object at 0x7f5a7c4aab88; dsn: 'user=postgres password=xxx dbname=tests host=127.0.0.1 port=13987 options=''', closed: 0> mock_package_sql = <function mock_package_sql.<locals>.get_sql_for_package_mock at 0x7f5a7c227d90>
1 msJenkins > .tox.py3-core-db-server-slow-cover.lib.python3.7.site-packages.swh.core.api.tests.test_async::test_async_decode_data_empty_cases[None]
1 msJenkins > .tox.py3-core-db-server-slow-cover.lib.python3.7.site-packages.swh.core.api.tests.test_async::test_async_decode_data_empty_cases[]
View Full Test Results (3 Failed · 251 Passed · 1 Skipped)

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes

Build is green

Patch application report for D7064 (id=25636)

Could not rebase; Attempt merge onto aba5c80765...

Updating aba5c80..855262e
Fast-forward
 MANIFEST.in                                        |   3 +-
 swh/core/cli/db.py                                 | 309 +++++++++-------
 swh/core/db/db_utils.py                            | 388 ++++++++++++++++++++-
 swh/core/db/sql/35-dbmetadata.sql                  |  28 ++
 swh/core/db/tests/conftest.py                      |  42 +++
 .../tests/data/cli/{1-schema.sql => 30-schema.sql} |   2 +-
 .../db/tests/data/cli/{3-func.sql => 40-funcs.sql} |   0
 .../db/tests/data/cli/{4-data.sql => 50-data.sql}  |   2 +-
 .../db/tests/data/cli_new/0-superuser-init.sql     |   1 +
 swh/core/db/tests/data/cli_new/30-schema.sql       |   6 +
 swh/core/db/tests/data/cli_new/40-funcs.sql        |   6 +
 swh/core/db/tests/data/cli_new/50-data.sql         |   2 +
 swh/core/db/tests/data/cli_new/upgrades/001.sql    |   5 +
 swh/core/db/tests/data/cli_new/upgrades/002.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/003.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/004.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/005.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/006.sql    |   7 +
 swh/core/db/tests/test_cli.py                      | 204 ++++++++---
 swh/core/db/tests/test_db_utils.py                 | 117 +++++++
 swh/core/sql/log-schema.sql                        |  33 --
 21 files changed, 941 insertions(+), 230 deletions(-)
 create mode 100644 swh/core/db/sql/35-dbmetadata.sql
 rename swh/core/db/tests/data/cli/{1-schema.sql => 30-schema.sql} (87%)
 rename swh/core/db/tests/data/cli/{3-func.sql => 40-funcs.sql} (100%)
 rename swh/core/db/tests/data/cli/{4-data.sql => 50-data.sql} (73%)
 create mode 100644 swh/core/db/tests/data/cli_new/0-superuser-init.sql
 create mode 100644 swh/core/db/tests/data/cli_new/30-schema.sql
 create mode 100644 swh/core/db/tests/data/cli_new/40-funcs.sql
 create mode 100644 swh/core/db/tests/data/cli_new/50-data.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/001.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/002.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/003.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/004.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/005.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/006.sql
 create mode 100644 swh/core/db/tests/test_db_utils.py
 delete mode 100644 swh/core/sql/log-schema.sql
Changes applied before test
commit 855262e3496f024d0c0a7f8639dfd8efbecab27d
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 15:43:09 2022 +0100

    Add a 'swh db upgrade' command
    
    this new command allows to handle the database migration from the
    current version to the latest version advertized but the backend
    datastore.
    
    Thie feature is only working for "new style" swh.core.db based
    datastores.

commit 1a4ab4e856b2d5b7c97bc955c8d0373a95a0fd66
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 14:51:43 2022 +0100

    Make 'swh db init' fill the dbversion table
    
    for this feature to work properly, the backend datastore package must
    provide the 'get_datastore' factory function in its root namespace and
    the datastore instance must provide a 'get_current_version()' method.
    
    This also means the canonical cli usage for the `swh db init` is now
    using the config file (via `--config-file` or `SWH_CONFIG_FILENAME`)
    instead of giving only the db cnx string (`--db-name`), so that the
    backend datastore class can be instanciated using the `get_datastore()`
    factory function.
    
    However a '--initial-version' cli option has been added to 'swh db init'
    to make it easier, especially for unit tests.

commit 58e4f342eeeab1638e57d090d584435c91cf506d
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 17:54:01 2022 +0100

    Use a standard postgresql db fixture in test_cli
    
    instead of a custom 'test_db' (which did not truncate dbversion and
    origin tables, not sure what the intent was for this).

commit 2f2b047ffd906acf0d64d8f0d94e192e49759480
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 15:02:51 2022 +0100

    Add support for dbversion and dbmodule handling in swh db init
    
    for now, managing the dbversion table (storing the db schema upgrade
    history) is partially the responsibility of the swh modules implementing
    a datastore (swh-storage etc.), and the actual swh module was not stored
    in the database.
    
    This adds support for both the dbversion and dbmodule tables management
    in swh.core.db.
    
    The `swh db init` command now creates both these tables and initialise
    their values if possible.
    For this, it introduces a new common "API" for swh.core.db based datastores:
    
    - a swh module implementing a datastore is expected to have a
      `get_datastore` function in its top namespace; this function is
      typically the actual `get_storage`, `get_scheduler` etc. functions
    - this factory function is expected to use the "postgresql" cls for the
      datastore based on swh.core.db,
    - the datastore instance (eg. instance of the swh.storage.postgresql.Storage
      class) is expected to have a `get_current_version()` method (returning
      an int).
    
    This revision also provides a new `swh db version` command displaying
    the current (code) version (if avalable), the db version (possibly the whole
    version history) and flavor (if any) for the datastore given as argument.
    
    It should be bacward compatible with eisting swh datastore modules (i.e.
    not adapter to this new API).

commit 7774e8e504afe02b46dc075e3ce9cc82bcc59ab8
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 16:00:43 2022 +0100

    Refactor the mock_package_sql fixture to allow multiple sql script set
    
    will be used to implement several test scenarios in following commits.
    Also rename existing sql scripts with a "legit" numbering scheme.

commit 36bc18267c911abb85b6a1cfc3321942e0359369
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 14:54:39 2022 +0100

    Make `swh db init` retrieve the db cnx uri from the config file

commit de1c894e3fa88b3e1e2ba1e7c7dbfc77f7ea4b51
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 14:49:41 2022 +0100

    Move utility functions from cli.db to db.db_utils

commit e4117fcf51a256c5b708f909c818f452c794c0f8
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 10:07:35 2022 +0100

    Remove the old and unused log-schema.sql file

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

Build is green

Patch application report for D7064 (id=25640)

Could not rebase; Attempt merge onto aba5c80765...

Updating aba5c80..e32fc76
Fast-forward
 MANIFEST.in                                        |   3 +-
 swh/core/cli/db.py                                 | 334 ++++++++++--------
 swh/core/db/db_utils.py                            | 388 ++++++++++++++++++++-
 swh/core/db/sql/35-dbmetadata.sql                  |  28 ++
 swh/core/db/tests/conftest.py                      |  42 +++
 .../tests/data/cli/{1-schema.sql => 30-schema.sql} |   2 +-
 .../db/tests/data/cli/{3-func.sql => 40-funcs.sql} |   0
 .../db/tests/data/cli/{4-data.sql => 50-data.sql}  |   2 +-
 .../db/tests/data/cli_new/0-superuser-init.sql     |   1 +
 swh/core/db/tests/data/cli_new/30-schema.sql       |   6 +
 swh/core/db/tests/data/cli_new/40-funcs.sql        |   6 +
 swh/core/db/tests/data/cli_new/50-data.sql         |   2 +
 swh/core/db/tests/data/cli_new/upgrades/001.sql    |   5 +
 swh/core/db/tests/data/cli_new/upgrades/002.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/003.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/004.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/005.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/006.sql    |   7 +
 swh/core/db/tests/test_cli.py                      | 204 ++++++++---
 swh/core/db/tests/test_db_utils.py                 | 117 +++++++
 swh/core/sql/log-schema.sql                        |  33 --
 21 files changed, 956 insertions(+), 240 deletions(-)
 create mode 100644 swh/core/db/sql/35-dbmetadata.sql
 rename swh/core/db/tests/data/cli/{1-schema.sql => 30-schema.sql} (87%)
 rename swh/core/db/tests/data/cli/{3-func.sql => 40-funcs.sql} (100%)
 rename swh/core/db/tests/data/cli/{4-data.sql => 50-data.sql} (73%)
 create mode 100644 swh/core/db/tests/data/cli_new/0-superuser-init.sql
 create mode 100644 swh/core/db/tests/data/cli_new/30-schema.sql
 create mode 100644 swh/core/db/tests/data/cli_new/40-funcs.sql
 create mode 100644 swh/core/db/tests/data/cli_new/50-data.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/001.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/002.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/003.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/004.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/005.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/006.sql
 create mode 100644 swh/core/db/tests/test_db_utils.py
 delete mode 100644 swh/core/sql/log-schema.sql
Changes applied before test
commit e32fc760e14051f7b16d9bfa646c3eb44fa8d46a
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 15:43:09 2022 +0100

    Add a 'swh db upgrade' command
    
    this new command allows to handle the database migration from the
    current version to the latest version advertized but the backend
    datastore.
    
    Thie feature is only working for "new style" swh.core.db based
    datastores.

commit a8a821485532a09a4b2fecbb897d70aeb6d1cc1d
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 14:51:43 2022 +0100

    Make 'swh db init' fill the dbversion table
    
    for this feature to work properly, the backend datastore package must
    provide the 'get_datastore' factory function in its root namespace and
    the datastore instance must provide a 'get_current_version()' method.
    
    This also means the canonical cli usage for the `swh db init` is now
    using the config file (via `--config-file` or `SWH_CONFIG_FILENAME`)
    instead of giving only the db cnx string (`--db-name`), so that the
    backend datastore class can be instanciated using the `get_datastore()`
    factory function.
    
    However a '--initial-version' cli option has been added to 'swh db init'
    to make it easier, especially for unit tests.

commit ba7318d74ef473a57c2791fad0ae32c55a74e6cf
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 17:54:01 2022 +0100

    Use a standard postgresql db fixture in test_cli
    
    instead of a custom 'test_db' (which did not truncate dbversion and
    origin tables, not sure what the intent was for this).

commit 7036f2d8cf38f8d5b5cb9a2df02235012e12ddcd
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 15:02:51 2022 +0100

    Add support for dbversion and dbmodule handling in swh db init
    
    for now, managing the dbversion table (storing the db schema upgrade
    history) is partially the responsibility of the swh modules implementing
    a datastore (swh-storage etc.), and the actual swh module was not stored
    in the database.
    
    This adds support for both the dbversion and dbmodule tables management
    in swh.core.db.
    
    The `swh db init` command now creates both these tables and initialise
    their values if possible.
    For this, it introduces a new common "API" for swh.core.db based datastores:
    
    - a swh module implementing a datastore is expected to have a
      `get_datastore` function in its top namespace; this function is
      typically the actual `get_storage`, `get_scheduler` etc. functions
    - this factory function is expected to use the "postgresql" cls for the
      datastore based on swh.core.db,
    - the datastore instance (eg. instance of the swh.storage.postgresql.Storage
      class) is expected to have a `get_current_version()` method (returning
      an int).
    
    This revision also provides a new `swh db version` command displaying
    the current (code) version (if avalable), the db version (possibly the whole
    version history) and flavor (if any) for the datastore given as argument.
    
    It should be bacward compatible with eisting swh datastore modules (i.e.
    not adapter to this new API).

commit d1ab8a3ab5cfef97918b5168c0fa15f5c26c0e18
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 16:00:43 2022 +0100

    Refactor the mock_package_sql fixture to allow multiple sql script set
    
    will be used to implement several test scenarios in following commits.
    Also rename existing sql scripts with a "legit" numbering scheme.

commit b11933f997e32db1b59be095a4dfe5d0c3be912b
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 14:54:39 2022 +0100

    Make `swh db init` retrieve the db cnx uri from the config file

commit de1c894e3fa88b3e1e2ba1e7c7dbfc77f7ea4b51
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 14:49:41 2022 +0100

    Move utility functions from cli.db to db.db_utils

commit e4117fcf51a256c5b708f909c818f452c794c0f8
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 10:07:35 2022 +0100

    Remove the old and unused log-schema.sql file

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

Looks good, but I have lots of (more or less) stylistic comments below

swh/core/cli/db.py
343–345

Why not logging? And shouldn't it be a hard error with non-zero return code? (same questions below)

360

shouldn't to_version > ds_version be a hard error, instead of silently fixing it?

swh/core/db/db_utils.py
167–171

if you make get_sql_for_package return a pathlib.Path (by replacing glob.glob(path.join(sqldir, "*.sql")) with sqldir.glob("*.sql")), then you can also simplify this like so

174

ditto

460

please add a docstring

swh/core/db/tests/test_cli.py
284–288

this should work

297–303

also dedent here?

swh/core/db/tests/test_db_utils.py
1–2

missing copyright notice btw

Build is green

Patch application report for D7064 (id=25935)

Could not rebase; Attempt merge onto 5046a95f05...

Updating 5046a95..95f137c
Fast-forward
 MANIFEST.in                                        |   3 +-
 swh/core/cli/db.py                                 | 334 ++++++++++--------
 swh/core/db/db_utils.py                            | 388 ++++++++++++++++++++-
 swh/core/db/sql/35-dbmetadata.sql                  |  28 ++
 swh/core/db/tests/conftest.py                      |  42 +++
 .../tests/data/cli/{1-schema.sql => 30-schema.sql} |   2 +-
 .../db/tests/data/cli/{3-func.sql => 40-funcs.sql} |   0
 .../db/tests/data/cli/{4-data.sql => 50-data.sql}  |   2 +-
 .../db/tests/data/cli_new/0-superuser-init.sql     |   1 +
 swh/core/db/tests/data/cli_new/30-schema.sql       |   6 +
 swh/core/db/tests/data/cli_new/40-funcs.sql        |   6 +
 swh/core/db/tests/data/cli_new/50-data.sql         |   2 +
 swh/core/db/tests/data/cli_new/upgrades/001.sql    |   5 +
 swh/core/db/tests/data/cli_new/upgrades/002.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/003.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/004.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/005.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/006.sql    |   7 +
 swh/core/db/tests/test_cli.py                      | 204 ++++++++---
 swh/core/db/tests/test_db_utils.py                 | 117 +++++++
 swh/core/sql/log-schema.sql                        |  33 --
 swh/core/tests/test_utils.py                       | 193 +++++-----
 swh/core/utils.py                                  |  16 +-
 23 files changed, 1065 insertions(+), 340 deletions(-)
 create mode 100644 swh/core/db/sql/35-dbmetadata.sql
 rename swh/core/db/tests/data/cli/{1-schema.sql => 30-schema.sql} (87%)
 rename swh/core/db/tests/data/cli/{3-func.sql => 40-funcs.sql} (100%)
 rename swh/core/db/tests/data/cli/{4-data.sql => 50-data.sql} (73%)
 create mode 100644 swh/core/db/tests/data/cli_new/0-superuser-init.sql
 create mode 100644 swh/core/db/tests/data/cli_new/30-schema.sql
 create mode 100644 swh/core/db/tests/data/cli_new/40-funcs.sql
 create mode 100644 swh/core/db/tests/data/cli_new/50-data.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/001.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/002.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/003.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/004.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/005.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/006.sql
 create mode 100644 swh/core/db/tests/test_db_utils.py
 delete mode 100644 swh/core/sql/log-schema.sql
Changes applied before test
commit 95f137c38bb41d6b60669402a82ac829145c5121
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 15:43:09 2022 +0100

    Add a 'swh db upgrade' command
    
    this new command allows to handle the database migration from the
    current version to the latest version advertized but the backend
    datastore.
    
    Thie feature is only working for "new style" swh.core.db based
    datastores.

commit 20e63a8c5105972d402264beb8d0fb84704142cd
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 14:51:43 2022 +0100

    Make 'swh db init' fill the dbversion table
    
    for this feature to work properly, the backend datastore package must
    provide the 'get_datastore' factory function in its root namespace and
    the datastore instance must provide a 'get_current_version()' method.
    
    This also means the canonical cli usage for the `swh db init` is now
    using the config file (via `--config-file` or `SWH_CONFIG_FILENAME`)
    instead of giving only the db cnx string (`--db-name`), so that the
    backend datastore class can be instanciated using the `get_datastore()`
    factory function.
    
    However a '--initial-version' cli option has been added to 'swh db init'
    to make it easier, especially for unit tests.

commit ec245207af61185298b087955bc609a0704a126c
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 17:54:01 2022 +0100

    Use a standard postgresql db fixture in test_cli
    
    instead of a custom 'test_db' (which did not truncate dbversion and
    origin tables, not sure what the intent was for this).

commit 93c9c701d28bf38b37bcf8ba5607fad3116c0087
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 15:02:51 2022 +0100

    Add support for dbversion and dbmodule handling in swh db init
    
    for now, managing the dbversion table (storing the db schema upgrade
    history) is partially the responsibility of the swh modules implementing
    a datastore (swh-storage etc.), and the actual swh module was not stored
    in the database.
    
    This adds support for both the dbversion and dbmodule tables management
    in swh.core.db.
    
    The `swh db init` command now creates both these tables and initialise
    their values if possible.
    For this, it introduces a new common "API" for swh.core.db based datastores:
    
    - a swh module implementing a datastore is expected to have a
      `get_datastore` function in its top namespace; this function is
      typically the actual `get_storage`, `get_scheduler` etc. functions
    - this factory function is expected to use the "postgresql" cls for the
      datastore based on swh.core.db,
    - the datastore instance (eg. instance of the swh.storage.postgresql.Storage
      class) is expected to have a `get_current_version()` method (returning
      an int).
    
    This revision also provides a new `swh db version` command displaying
    the current (code) version (if avalable), the db version (possibly the whole
    version history) and flavor (if any) for the datastore given as argument.
    
    It should be bacward compatible with eisting swh datastore modules (i.e.
    not adapter to this new API).

commit b1e449fd91ebcc403966a79fd62d449921fcf550
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 16:00:43 2022 +0100

    Refactor the mock_package_sql fixture to allow multiple sql script set
    
    will be used to implement several test scenarios in following commits.
    Also rename existing sql scripts with a "legit" numbering scheme.

commit 3139d3344e232db83edacc95884604e47f0461cb
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Feb 11 12:10:43 2022 +0100

    Fix a bug in numfile_sortkey and add tests for it

commit 68dc6811967ff0f6fb1296f9687d22d7f0fb684b
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Feb 11 11:59:56 2022 +0100

    Convert test_utils to pytest

commit 3e43730df6150168ca99cb083bd2e60feb4a2771
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 14:54:39 2022 +0100

    Make `swh db init` retrieve the db cnx uri from the config file

commit 459da96c1e6b6044deb874e0e668606c3da3e43c
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 14:49:41 2022 +0100

    Move utility functions from cli.db to db.db_utils

commit 932eed4863baedf011b4a59d0227331d37db1dfd
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 10:07:35 2022 +0100

    Remove the old and unused log-schema.sql file

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

Build has FAILED

Patch application report for D7064 (id=25947)

Could not rebase; Attempt merge onto 3139d3344e...

Updating 3139d33..5712a5d
Fast-forward
 MANIFEST.in                                        |   2 +
 swh/core/cli/db.py                                 | 179 +++++++++++++++-
 swh/core/db/db_utils.py                            | 228 ++++++++++++++++++++-
 swh/core/db/sql/35-dbmetadata.sql                  |  28 +++
 swh/core/db/tests/conftest.py                      |  42 ++++
 .../tests/data/cli/{1-schema.sql => 30-schema.sql} |   2 +-
 .../db/tests/data/cli/{3-func.sql => 40-funcs.sql} |   0
 .../db/tests/data/cli/{4-data.sql => 50-data.sql}  |   2 +-
 .../db/tests/data/cli_new/0-superuser-init.sql     |   1 +
 swh/core/db/tests/data/cli_new/30-schema.sql       |   6 +
 swh/core/db/tests/data/cli_new/40-funcs.sql        |   6 +
 swh/core/db/tests/data/cli_new/50-data.sql         |   2 +
 swh/core/db/tests/data/cli_new/upgrades/001.sql    |   5 +
 swh/core/db/tests/data/cli_new/upgrades/002.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/003.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/004.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/005.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/006.sql    |   7 +
 swh/core/db/tests/test_cli.py                      | 197 +++++++++++++-----
 swh/core/db/tests/test_db_utils.py                 | 117 +++++++++++
 20 files changed, 779 insertions(+), 61 deletions(-)
 create mode 100644 swh/core/db/sql/35-dbmetadata.sql
 rename swh/core/db/tests/data/cli/{1-schema.sql => 30-schema.sql} (87%)
 rename swh/core/db/tests/data/cli/{3-func.sql => 40-funcs.sql} (100%)
 rename swh/core/db/tests/data/cli/{4-data.sql => 50-data.sql} (73%)
 create mode 100644 swh/core/db/tests/data/cli_new/0-superuser-init.sql
 create mode 100644 swh/core/db/tests/data/cli_new/30-schema.sql
 create mode 100644 swh/core/db/tests/data/cli_new/40-funcs.sql
 create mode 100644 swh/core/db/tests/data/cli_new/50-data.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/001.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/002.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/003.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/004.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/005.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/006.sql
 create mode 100644 swh/core/db/tests/test_db_utils.py
Changes applied before test
commit 5712a5d290966b13242d3a790bd29e6a442fa87b
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 15:43:09 2022 +0100

    Add a 'swh db upgrade' command
    
    this new command allows to handle the database migration from the
    current version to the latest version advertized but the backend
    datastore.
    
    Thie feature is only working for "new style" swh.core.db based
    datastores.

commit cfe1e6059e85bbe8d1ad39a68f9953b478dcb294
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 14:51:43 2022 +0100

    Make 'swh db init' fill the dbversion table
    
    for this feature to work properly, the backend datastore package must
    provide the 'get_datastore' factory function in its root namespace and
    the datastore instance must provide a 'get_current_version()' method.
    
    This also means the canonical cli usage for the `swh db init` is now
    using the config file (via `--config-file` or `SWH_CONFIG_FILENAME`)
    instead of giving only the db cnx string (`--db-name`), so that the
    backend datastore class can be instanciated using the `get_datastore()`
    factory function.
    
    However a '--initial-version' cli option has been added to 'swh db init'
    to make it easier, especially for unit tests.

commit 2e184856ac6ff8af359d6112441ccbf837e6fed8
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 17:54:01 2022 +0100

    Use a standard postgresql db fixture in test_cli
    
    instead of a custom 'test_db' (which did not truncate dbversion and
    origin tables, not sure what the intent was for this).

commit 943a813e318248e0c51a35d7866dbb145290c82a
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 15:02:51 2022 +0100

    Add support for dbversion and dbmodule handling in swh db init
    
    for now, managing the dbversion table (storing the db schema upgrade
    history) is partially the responsibility of the swh modules implementing
    a datastore (swh-storage etc.), and the actual swh module was not stored
    in the database.
    
    This adds support for both the dbversion and dbmodule tables management
    in swh.core.db.
    
    The `swh db init` command now creates both these tables and initialize
    their values if possible.
    For this, it introduces a new common "API" for swh.core.db based datastores:
    
    - a swh module implementing a datastore is expected to have a
      `get_datastore` function in its top namespace; this function is
      typically the actual `get_storage`, `get_scheduler` etc. functions
    - this factory function is expected to use the "postgresql" cls for the
      datastore based on swh.core.db,
    - the datastore instance (eg. instance of the swh.storage.postgresql.Storage
      class) is expected to have a `get_current_version()` method (returning
      an int).
    
    This revision also provides a new `swh db version` command displaying
    the current (code) version (if available), the db version (possibly the whole
    version history) and flavor (if any) for the datastore given as argument.
    
    It should be backward compatible with existing swh datastore modules (i.e.
    not yet adapted to this new API).

commit b1e449fd91ebcc403966a79fd62d449921fcf550
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 16:00:43 2022 +0100

    Refactor the mock_package_sql fixture to allow multiple sql script set
    
    will be used to implement several test scenarios in following commits.
    Also rename existing sql scripts with a "legit" numbering scheme.

Link to build: https://jenkins.softwareheritage.org/job/DCORE/job/tests-on-diff/306/
See console output for more information: https://jenkins.softwareheritage.org/job/DCORE/job/tests-on-diff/306/console

Build is green

Patch application report for D7064 (id=25956)

Could not rebase; Attempt merge onto 3139d3344e...

Updating 3139d33..fc451f2
Fast-forward
 MANIFEST.in                                        |   2 +
 swh/core/cli/db.py                                 | 179 +++++++++++++++-
 swh/core/db/db_utils.py                            | 228 ++++++++++++++++++++-
 swh/core/db/sql/35-dbmetadata.sql                  |  28 +++
 swh/core/db/tests/conftest.py                      |  42 ++++
 .../tests/data/cli/{1-schema.sql => 30-schema.sql} |   2 +-
 .../db/tests/data/cli/{3-func.sql => 40-funcs.sql} |   0
 .../db/tests/data/cli/{4-data.sql => 50-data.sql}  |   2 +-
 .../db/tests/data/cli_new/0-superuser-init.sql     |   1 +
 swh/core/db/tests/data/cli_new/30-schema.sql       |   6 +
 swh/core/db/tests/data/cli_new/40-funcs.sql        |   6 +
 swh/core/db/tests/data/cli_new/50-data.sql         |   2 +
 swh/core/db/tests/data/cli_new/upgrades/001.sql    |   5 +
 swh/core/db/tests/data/cli_new/upgrades/002.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/003.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/004.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/005.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/006.sql    |   7 +
 swh/core/db/tests/test_cli.py                      | 197 +++++++++++++-----
 swh/core/db/tests/test_db_utils.py                 | 117 +++++++++++
 20 files changed, 779 insertions(+), 61 deletions(-)
 create mode 100644 swh/core/db/sql/35-dbmetadata.sql
 rename swh/core/db/tests/data/cli/{1-schema.sql => 30-schema.sql} (87%)
 rename swh/core/db/tests/data/cli/{3-func.sql => 40-funcs.sql} (100%)
 rename swh/core/db/tests/data/cli/{4-data.sql => 50-data.sql} (73%)
 create mode 100644 swh/core/db/tests/data/cli_new/0-superuser-init.sql
 create mode 100644 swh/core/db/tests/data/cli_new/30-schema.sql
 create mode 100644 swh/core/db/tests/data/cli_new/40-funcs.sql
 create mode 100644 swh/core/db/tests/data/cli_new/50-data.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/001.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/002.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/003.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/004.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/005.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/006.sql
 create mode 100644 swh/core/db/tests/test_db_utils.py
Changes applied before test
commit fc451f2b86eff00fb5dea99c919d5187e5e3c5b5
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 15:43:09 2022 +0100

    Add a 'swh db upgrade' command
    
    this new command allows to handle the database migration from the
    current version to the latest version advertized but the backend
    datastore.
    
    Thie feature is only working for "new style" swh.core.db based
    datastores.

commit 330a9d31f5f188dbc457f6cda349bcafa09e396e
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 14:51:43 2022 +0100

    Make 'swh db init' fill the dbversion table
    
    for this feature to work properly, the backend datastore package must
    provide the 'get_datastore' factory function in its root namespace and
    the datastore instance must provide a 'get_current_version()' method.
    
    This also means the canonical cli usage for the `swh db init` is now
    using the config file (via `--config-file` or `SWH_CONFIG_FILENAME`)
    instead of giving only the db cnx string (`--db-name`), so that the
    backend datastore class can be instanciated using the `get_datastore()`
    factory function.
    
    However a '--initial-version' cli option has been added to 'swh db init'
    to make it easier, especially for unit tests.

commit 2e184856ac6ff8af359d6112441ccbf837e6fed8
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 17:54:01 2022 +0100

    Use a standard postgresql db fixture in test_cli
    
    instead of a custom 'test_db' (which did not truncate dbversion and
    origin tables, not sure what the intent was for this).

commit 943a813e318248e0c51a35d7866dbb145290c82a
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 15:02:51 2022 +0100

    Add support for dbversion and dbmodule handling in swh db init
    
    for now, managing the dbversion table (storing the db schema upgrade
    history) is partially the responsibility of the swh modules implementing
    a datastore (swh-storage etc.), and the actual swh module was not stored
    in the database.
    
    This adds support for both the dbversion and dbmodule tables management
    in swh.core.db.
    
    The `swh db init` command now creates both these tables and initialize
    their values if possible.
    For this, it introduces a new common "API" for swh.core.db based datastores:
    
    - a swh module implementing a datastore is expected to have a
      `get_datastore` function in its top namespace; this function is
      typically the actual `get_storage`, `get_scheduler` etc. functions
    - this factory function is expected to use the "postgresql" cls for the
      datastore based on swh.core.db,
    - the datastore instance (eg. instance of the swh.storage.postgresql.Storage
      class) is expected to have a `get_current_version()` method (returning
      an int).
    
    This revision also provides a new `swh db version` command displaying
    the current (code) version (if available), the db version (possibly the whole
    version history) and flavor (if any) for the datastore given as argument.
    
    It should be backward compatible with existing swh datastore modules (i.e.
    not yet adapted to this new API).

commit b1e449fd91ebcc403966a79fd62d449921fcf550
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 16:00:43 2022 +0100

    Refactor the mock_package_sql fixture to allow multiple sql script set
    
    will be used to implement several test scenarios in following commits.
    Also rename existing sql scripts with a "legit" numbering scheme.

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

Build has FAILED

Patch application report for D7064 (id=25985)

Could not rebase; Attempt merge onto 3139d3344e...

Updating 3139d33..528371a
Fast-forward
 MANIFEST.in                                        |   2 +
 swh/core/cli/db.py                                 | 179 +++++++++++++++-
 swh/core/db/db_utils.py                            | 228 ++++++++++++++++++++-
 swh/core/db/sql/35-dbmetadata.sql                  |  28 +++
 swh/core/db/tests/conftest.py                      |  42 ++++
 .../tests/data/cli/{1-schema.sql => 30-schema.sql} |   2 +-
 .../db/tests/data/cli/{3-func.sql => 40-funcs.sql} |   0
 .../db/tests/data/cli/{4-data.sql => 50-data.sql}  |   2 +-
 .../db/tests/data/cli_new/0-superuser-init.sql     |   1 +
 swh/core/db/tests/data/cli_new/30-schema.sql       |   6 +
 swh/core/db/tests/data/cli_new/40-funcs.sql        |   6 +
 swh/core/db/tests/data/cli_new/50-data.sql         |   2 +
 swh/core/db/tests/data/cli_new/upgrades/001.sql    |   5 +
 swh/core/db/tests/data/cli_new/upgrades/002.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/003.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/004.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/005.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/006.sql    |   7 +
 swh/core/db/tests/test_cli.py                      | 197 +++++++++++++-----
 swh/core/db/tests/test_db_utils.py                 | 118 +++++++++++
 20 files changed, 780 insertions(+), 61 deletions(-)
 create mode 100644 swh/core/db/sql/35-dbmetadata.sql
 rename swh/core/db/tests/data/cli/{1-schema.sql => 30-schema.sql} (87%)
 rename swh/core/db/tests/data/cli/{3-func.sql => 40-funcs.sql} (100%)
 rename swh/core/db/tests/data/cli/{4-data.sql => 50-data.sql} (73%)
 create mode 100644 swh/core/db/tests/data/cli_new/0-superuser-init.sql
 create mode 100644 swh/core/db/tests/data/cli_new/30-schema.sql
 create mode 100644 swh/core/db/tests/data/cli_new/40-funcs.sql
 create mode 100644 swh/core/db/tests/data/cli_new/50-data.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/001.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/002.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/003.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/004.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/005.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/006.sql
 create mode 100644 swh/core/db/tests/test_db_utils.py
Changes applied before test
commit 528371a0f84aff02604ea51c6e24fe97ebd3de7a
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 15:43:09 2022 +0100

    Add a 'swh db upgrade' command
    
    this new command allows to handle the database migration from the
    current version to the latest version advertized but the backend
    datastore.
    
    Thie feature is only working for "new style" swh.core.db based
    datastores.

commit 544e184045403c735c33e6029f8bb7fa1a4df5cb
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 14:51:43 2022 +0100

    Make 'swh db init' fill the dbversion table
    
    for this feature to work properly, the backend datastore package must
    provide the 'get_datastore' factory function in its root namespace and
    the datastore instance must provide a 'get_current_version()' method.
    
    This also means the canonical cli usage for the `swh db init` is now
    using the config file (via `--config-file` or `SWH_CONFIG_FILENAME`)
    instead of giving only the db cnx string (`--db-name`), so that the
    backend datastore class can be instanciated using the `get_datastore()`
    factory function.
    
    However a '--initial-version' cli option has been added to 'swh db init'
    to make it easier, especially for unit tests.

commit adeb54f770209284f0924a7e4d5bd5977a567527
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 17:54:01 2022 +0100

    Use a standard postgresql db fixture in test_cli
    
    instead of a custom 'test_db' (which did not truncate dbversion and
    origin tables, not sure what the intent was for this).

commit d50695c3feb57b1e21d93e78364a943232fe91ff
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 15:02:51 2022 +0100

    Add support for dbversion and dbmodule handling in swh db init
    
    for now, managing the dbversion table (storing the db schema upgrade
    history) is partially the responsibility of the swh modules implementing
    a datastore (swh-storage etc.), and the actual swh module was not stored
    in the database.
    
    This adds support for both the dbversion and dbmodule tables management
    in swh.core.db.
    
    The `swh db init` command now creates both these tables and initialize
    their values if possible.
    For this, it introduces a new common "API" for swh.core.db based datastores:
    
    - a swh module implementing a datastore is expected to have a
      `get_datastore` function in its top namespace; this function is
      typically the actual `get_storage`, `get_scheduler` etc. functions
    - this factory function is expected to use the "postgresql" cls for the
      datastore based on swh.core.db,
    - the datastore instance (eg. instance of the swh.storage.postgresql.Storage
      class) is expected to have a `get_current_version()` method (returning
      an int).
    
    This revision also provides a new `swh db version` command displaying
    the current (code) version (if available), the db version (possibly the whole
    version history) and flavor (if any) for the datastore given as argument.
    
    It should be backward compatible with existing swh datastore modules (i.e.
    not yet adapted to this new API).

commit b1e449fd91ebcc403966a79fd62d449921fcf550
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 16:00:43 2022 +0100

    Refactor the mock_package_sql fixture to allow multiple sql script set
    
    will be used to implement several test scenarios in following commits.
    Also rename existing sql scripts with a "legit" numbering scheme.

Link to build: https://jenkins.softwareheritage.org/job/DCORE/job/tests-on-diff/317/
See console output for more information: https://jenkins.softwareheritage.org/job/DCORE/job/tests-on-diff/317/console

ardumont added a subscriber: ardumont.

Looks like it's going the right way.
Requesting changes for a few things (see inline).

I'm overall confused about one thing. Shall we still need to add the version bump in the sql files or not?

swh/core/cli/db.py
319
323
343–345

it's consistent with the other diff changes.
It'd be a hassle to change now.

Plus the secho is probably nicer to the eye regarding colors (when someone is not color-blind that is)

360

+1

that'd be consistent with what you said to me earlier in another about a cli misuse.

swh/core/db/db_utils.py
154

Missing docstring.

465

what does that do?

my test in ipython which i probably did wrong said no to me

In [4]: s = '/tmp/'

In [5]: s/= 'hello'
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-5-f982401e46c4> in <module>
----> 1 s/= 'hello'

TypeError: unsupported operand type(s) for /=: 'str' and 'str'
465

ah maybe like you did below?

swh/core/db/tests/test_cli.py
270
284–288

not sure i prefer this suggestion in terms of readability though...

285

less to type...

308

top-level import

327

why?

339
swh/core/db/tests/test_db_utils.py
93

?

having read the next steps, it's possibly covered below.

This revision now requires changes to proceed.Feb 14 2022, 4:09 PM
douardda marked 2 inline comments as done.

typos, add sanity check in the 'swh db upgrade' command, add tests

make the swh db upgrade

  • fail if no version is found in the db
  • ask user (default Y) if s.he wants to install the dbmodule table if missing,
  • fail if to-version > current_version
  • fail if the stored dbmodule is different than the one given as cli argument

Build is green

Patch application report for D7064 (id=26017)

Could not rebase; Attempt merge onto 3139d3344e...

Updating 3139d33..b8dabdc
Fast-forward
 MANIFEST.in                                        |   2 +
 swh/core/cli/db.py                                 | 200 +++++++++++++++-
 swh/core/db/db_utils.py                            | 260 ++++++++++++++++++++-
 swh/core/db/sql/35-dbversion.sql                   |  18 ++
 swh/core/db/sql/36-dbmodule.sql                    |  15 ++
 swh/core/db/tests/conftest.py                      |  42 ++++
 .../tests/data/cli/{1-schema.sql => 30-schema.sql} |   2 +-
 .../db/tests/data/cli/{3-func.sql => 40-funcs.sql} |   0
 .../db/tests/data/cli/{4-data.sql => 50-data.sql}  |   2 +-
 .../db/tests/data/cli_new/0-superuser-init.sql     |   1 +
 swh/core/db/tests/data/cli_new/30-schema.sql       |   6 +
 swh/core/db/tests/data/cli_new/40-funcs.sql        |   6 +
 swh/core/db/tests/data/cli_new/50-data.sql         |   2 +
 swh/core/db/tests/data/cli_new/upgrades/001.sql    |   5 +
 swh/core/db/tests/data/cli_new/upgrades/002.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/003.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/004.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/005.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/006.sql    |   7 +
 swh/core/db/tests/test_cli.py                      | 220 +++++++++++++----
 swh/core/db/tests/test_db_utils.py                 | 166 +++++++++++++
 21 files changed, 909 insertions(+), 61 deletions(-)
 create mode 100644 swh/core/db/sql/35-dbversion.sql
 create mode 100644 swh/core/db/sql/36-dbmodule.sql
 rename swh/core/db/tests/data/cli/{1-schema.sql => 30-schema.sql} (87%)
 rename swh/core/db/tests/data/cli/{3-func.sql => 40-funcs.sql} (100%)
 rename swh/core/db/tests/data/cli/{4-data.sql => 50-data.sql} (73%)
 create mode 100644 swh/core/db/tests/data/cli_new/0-superuser-init.sql
 create mode 100644 swh/core/db/tests/data/cli_new/30-schema.sql
 create mode 100644 swh/core/db/tests/data/cli_new/40-funcs.sql
 create mode 100644 swh/core/db/tests/data/cli_new/50-data.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/001.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/002.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/003.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/004.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/005.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/006.sql
 create mode 100644 swh/core/db/tests/test_db_utils.py
Changes applied before test
commit b8dabdc0518dc5f7d121f87221a6cf8ceb9fb88a
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 15:43:09 2022 +0100

    Add a 'swh db upgrade' command
    
    this new command allows to handle the database migration from the
    current version to the latest version advertized but the backend
    datastore.
    
    Thie feature is only working for "new style" swh.core.db based
    datastores.

commit 544e184045403c735c33e6029f8bb7fa1a4df5cb
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 14:51:43 2022 +0100

    Make 'swh db init' fill the dbversion table
    
    for this feature to work properly, the backend datastore package must
    provide the 'get_datastore' factory function in its root namespace and
    the datastore instance must provide a 'get_current_version()' method.
    
    This also means the canonical cli usage for the `swh db init` is now
    using the config file (via `--config-file` or `SWH_CONFIG_FILENAME`)
    instead of giving only the db cnx string (`--db-name`), so that the
    backend datastore class can be instanciated using the `get_datastore()`
    factory function.
    
    However a '--initial-version' cli option has been added to 'swh db init'
    to make it easier, especially for unit tests.

commit adeb54f770209284f0924a7e4d5bd5977a567527
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 17:54:01 2022 +0100

    Use a standard postgresql db fixture in test_cli
    
    instead of a custom 'test_db' (which did not truncate dbversion and
    origin tables, not sure what the intent was for this).

commit d50695c3feb57b1e21d93e78364a943232fe91ff
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 15:02:51 2022 +0100

    Add support for dbversion and dbmodule handling in swh db init
    
    for now, managing the dbversion table (storing the db schema upgrade
    history) is partially the responsibility of the swh modules implementing
    a datastore (swh-storage etc.), and the actual swh module was not stored
    in the database.
    
    This adds support for both the dbversion and dbmodule tables management
    in swh.core.db.
    
    The `swh db init` command now creates both these tables and initialize
    their values if possible.
    For this, it introduces a new common "API" for swh.core.db based datastores:
    
    - a swh module implementing a datastore is expected to have a
      `get_datastore` function in its top namespace; this function is
      typically the actual `get_storage`, `get_scheduler` etc. functions
    - this factory function is expected to use the "postgresql" cls for the
      datastore based on swh.core.db,
    - the datastore instance (eg. instance of the swh.storage.postgresql.Storage
      class) is expected to have a `get_current_version()` method (returning
      an int).
    
    This revision also provides a new `swh db version` command displaying
    the current (code) version (if available), the db version (possibly the whole
    version history) and flavor (if any) for the datastore given as argument.
    
    It should be backward compatible with existing swh datastore modules (i.e.
    not yet adapted to this new API).

commit b1e449fd91ebcc403966a79fd62d449921fcf550
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 16:00:43 2022 +0100

    Refactor the mock_package_sql fixture to allow multiple sql script set
    
    will be used to implement several test scenarios in following commits.
    Also rename existing sql scripts with a "legit" numbering scheme.

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

douardda marked an inline comment as done.

typos and add missing docstrings

vlorent's suggestion of improving get_sql_for_package will come in a later diff (with other improvements for better testing); it was a bit too much work to rework this diff to do so.

Build is green

Patch application report for D7064 (id=26033)

Could not rebase; Attempt merge onto 3139d3344e...

Updating 3139d33..dfa0161
Fast-forward
 MANIFEST.in                                        |   2 +
 swh/core/cli/db.py                                 | 200 +++++++++++++++-
 swh/core/db/db_utils.py                            | 266 ++++++++++++++++++++-
 swh/core/db/sql/35-dbversion.sql                   |  18 ++
 swh/core/db/sql/36-dbmodule.sql                    |  15 ++
 swh/core/db/tests/conftest.py                      |  42 ++++
 .../tests/data/cli/{1-schema.sql => 30-schema.sql} |   2 +-
 .../db/tests/data/cli/{3-func.sql => 40-funcs.sql} |   0
 .../db/tests/data/cli/{4-data.sql => 50-data.sql}  |   2 +-
 .../db/tests/data/cli_new/0-superuser-init.sql     |   1 +
 swh/core/db/tests/data/cli_new/30-schema.sql       |   6 +
 swh/core/db/tests/data/cli_new/40-funcs.sql        |   6 +
 swh/core/db/tests/data/cli_new/50-data.sql         |   2 +
 swh/core/db/tests/data/cli_new/upgrades/001.sql    |   5 +
 swh/core/db/tests/data/cli_new/upgrades/002.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/003.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/004.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/005.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/006.sql    |   7 +
 swh/core/db/tests/test_cli.py                      | 220 +++++++++++++----
 swh/core/db/tests/test_db_utils.py                 | 166 +++++++++++++
 21 files changed, 915 insertions(+), 61 deletions(-)
 create mode 100644 swh/core/db/sql/35-dbversion.sql
 create mode 100644 swh/core/db/sql/36-dbmodule.sql
 rename swh/core/db/tests/data/cli/{1-schema.sql => 30-schema.sql} (87%)
 rename swh/core/db/tests/data/cli/{3-func.sql => 40-funcs.sql} (100%)
 rename swh/core/db/tests/data/cli/{4-data.sql => 50-data.sql} (73%)
 create mode 100644 swh/core/db/tests/data/cli_new/0-superuser-init.sql
 create mode 100644 swh/core/db/tests/data/cli_new/30-schema.sql
 create mode 100644 swh/core/db/tests/data/cli_new/40-funcs.sql
 create mode 100644 swh/core/db/tests/data/cli_new/50-data.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/001.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/002.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/003.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/004.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/005.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/006.sql
 create mode 100644 swh/core/db/tests/test_db_utils.py
Changes applied before test
commit dfa01610264a925ba02e33c6f2401d34fd34ec18
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 15:43:09 2022 +0100

    Add a 'swh db upgrade' command
    
    this new command allows to handle the database migration from the
    current version to the latest version advertized but the backend
    datastore.
    
    Thie feature is only working for "new style" swh.core.db based
    datastores.

commit 544e184045403c735c33e6029f8bb7fa1a4df5cb
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 14:51:43 2022 +0100

    Make 'swh db init' fill the dbversion table
    
    for this feature to work properly, the backend datastore package must
    provide the 'get_datastore' factory function in its root namespace and
    the datastore instance must provide a 'get_current_version()' method.
    
    This also means the canonical cli usage for the `swh db init` is now
    using the config file (via `--config-file` or `SWH_CONFIG_FILENAME`)
    instead of giving only the db cnx string (`--db-name`), so that the
    backend datastore class can be instanciated using the `get_datastore()`
    factory function.
    
    However a '--initial-version' cli option has been added to 'swh db init'
    to make it easier, especially for unit tests.

commit adeb54f770209284f0924a7e4d5bd5977a567527
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 17:54:01 2022 +0100

    Use a standard postgresql db fixture in test_cli
    
    instead of a custom 'test_db' (which did not truncate dbversion and
    origin tables, not sure what the intent was for this).

commit d50695c3feb57b1e21d93e78364a943232fe91ff
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 15:02:51 2022 +0100

    Add support for dbversion and dbmodule handling in swh db init
    
    for now, managing the dbversion table (storing the db schema upgrade
    history) is partially the responsibility of the swh modules implementing
    a datastore (swh-storage etc.), and the actual swh module was not stored
    in the database.
    
    This adds support for both the dbversion and dbmodule tables management
    in swh.core.db.
    
    The `swh db init` command now creates both these tables and initialize
    their values if possible.
    For this, it introduces a new common "API" for swh.core.db based datastores:
    
    - a swh module implementing a datastore is expected to have a
      `get_datastore` function in its top namespace; this function is
      typically the actual `get_storage`, `get_scheduler` etc. functions
    - this factory function is expected to use the "postgresql" cls for the
      datastore based on swh.core.db,
    - the datastore instance (eg. instance of the swh.storage.postgresql.Storage
      class) is expected to have a `get_current_version()` method (returning
      an int).
    
    This revision also provides a new `swh db version` command displaying
    the current (code) version (if available), the db version (possibly the whole
    version history) and flavor (if any) for the datastore given as argument.
    
    It should be backward compatible with existing swh datastore modules (i.e.
    not yet adapted to this new API).

commit b1e449fd91ebcc403966a79fd62d449921fcf550
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 16:00:43 2022 +0100

    Refactor the mock_package_sql fixture to allow multiple sql script set
    
    will be used to implement several test scenarios in following commits.
    Also rename existing sql scripts with a "legit" numbering scheme.

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

douardda marked 4 inline comments as done.

docstring and fix in get_sql_for_package

Build is green

Patch application report for D7064 (id=26040)

Could not rebase; Attempt merge onto 3139d3344e...

Updating 3139d33..1a187ca
Fast-forward
 MANIFEST.in                                        |   2 +
 swh/core/cli/db.py                                 | 200 ++++++++++++++-
 swh/core/db/db_utils.py                            | 271 ++++++++++++++++++++-
 swh/core/db/sql/35-dbversion.sql                   |  18 ++
 swh/core/db/sql/36-dbmodule.sql                    |  15 ++
 swh/core/db/tests/conftest.py                      |  42 ++++
 .../tests/data/cli/{1-schema.sql => 30-schema.sql} |   2 +-
 .../db/tests/data/cli/{3-func.sql => 40-funcs.sql} |   0
 .../db/tests/data/cli/{4-data.sql => 50-data.sql}  |   2 +-
 .../db/tests/data/cli_new/0-superuser-init.sql     |   1 +
 swh/core/db/tests/data/cli_new/30-schema.sql       |   6 +
 swh/core/db/tests/data/cli_new/40-funcs.sql        |   6 +
 swh/core/db/tests/data/cli_new/50-data.sql         |   2 +
 swh/core/db/tests/data/cli_new/upgrades/001.sql    |   5 +
 swh/core/db/tests/data/cli_new/upgrades/002.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/003.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/004.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/005.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/006.sql    |   7 +
 swh/core/db/tests/test_cli.py                      | 220 +++++++++++++----
 swh/core/db/tests/test_db_utils.py                 | 166 +++++++++++++
 21 files changed, 920 insertions(+), 61 deletions(-)
 create mode 100644 swh/core/db/sql/35-dbversion.sql
 create mode 100644 swh/core/db/sql/36-dbmodule.sql
 rename swh/core/db/tests/data/cli/{1-schema.sql => 30-schema.sql} (87%)
 rename swh/core/db/tests/data/cli/{3-func.sql => 40-funcs.sql} (100%)
 rename swh/core/db/tests/data/cli/{4-data.sql => 50-data.sql} (73%)
 create mode 100644 swh/core/db/tests/data/cli_new/0-superuser-init.sql
 create mode 100644 swh/core/db/tests/data/cli_new/30-schema.sql
 create mode 100644 swh/core/db/tests/data/cli_new/40-funcs.sql
 create mode 100644 swh/core/db/tests/data/cli_new/50-data.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/001.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/002.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/003.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/004.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/005.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/006.sql
 create mode 100644 swh/core/db/tests/test_db_utils.py
Changes applied before test
commit 1a187ca7787503d41ac13b2678b29f332d8fd200
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 15:43:09 2022 +0100

    Add a 'swh db upgrade' command
    
    this new command allows to handle the database migration from the
    current version to the latest version advertized but the backend
    datastore.
    
    Thie feature is only working for "new style" swh.core.db based
    datastores.

commit 544e184045403c735c33e6029f8bb7fa1a4df5cb
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 14:51:43 2022 +0100

    Make 'swh db init' fill the dbversion table
    
    for this feature to work properly, the backend datastore package must
    provide the 'get_datastore' factory function in its root namespace and
    the datastore instance must provide a 'get_current_version()' method.
    
    This also means the canonical cli usage for the `swh db init` is now
    using the config file (via `--config-file` or `SWH_CONFIG_FILENAME`)
    instead of giving only the db cnx string (`--db-name`), so that the
    backend datastore class can be instanciated using the `get_datastore()`
    factory function.
    
    However a '--initial-version' cli option has been added to 'swh db init'
    to make it easier, especially for unit tests.

commit adeb54f770209284f0924a7e4d5bd5977a567527
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 17:54:01 2022 +0100

    Use a standard postgresql db fixture in test_cli
    
    instead of a custom 'test_db' (which did not truncate dbversion and
    origin tables, not sure what the intent was for this).

commit d50695c3feb57b1e21d93e78364a943232fe91ff
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 15:02:51 2022 +0100

    Add support for dbversion and dbmodule handling in swh db init
    
    for now, managing the dbversion table (storing the db schema upgrade
    history) is partially the responsibility of the swh modules implementing
    a datastore (swh-storage etc.), and the actual swh module was not stored
    in the database.
    
    This adds support for both the dbversion and dbmodule tables management
    in swh.core.db.
    
    The `swh db init` command now creates both these tables and initialize
    their values if possible.
    For this, it introduces a new common "API" for swh.core.db based datastores:
    
    - a swh module implementing a datastore is expected to have a
      `get_datastore` function in its top namespace; this function is
      typically the actual `get_storage`, `get_scheduler` etc. functions
    - this factory function is expected to use the "postgresql" cls for the
      datastore based on swh.core.db,
    - the datastore instance (eg. instance of the swh.storage.postgresql.Storage
      class) is expected to have a `get_current_version()` method (returning
      an int).
    
    This revision also provides a new `swh db version` command displaying
    the current (code) version (if available), the db version (possibly the whole
    version history) and flavor (if any) for the datastore given as argument.
    
    It should be backward compatible with existing swh datastore modules (i.e.
    not yet adapted to this new API).

commit b1e449fd91ebcc403966a79fd62d449921fcf550
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 16:00:43 2022 +0100

    Refactor the mock_package_sql fixture to allow multiple sql script set
    
    will be used to implement several test scenarios in following commits.
    Also rename existing sql scripts with a "legit" numbering scheme.

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

swh/core/cli/db.py
343–345

Why not logging? And shouldn't it be a hard error with non-zero return code? (same questions below)

logging vs. click, well in the context of cli I though it makes sense to show nice colorful messages...

For the incorrect situation (old version of the database that does not have the dbmodule table), it should not be a hard error IMHO but it should trigger a dedicated migration step (aka create the said table and fill it).

360

agreed, would make more sense.

swh/core/db/db_utils.py
167–171

good thinking thanks

167–171

So I've done this but in a dedicated diff D7188

465

yes this is a big bad bug, which I did not notice because this function i mocked in the tests (which is also why i did D7188)...

Nonetheless, this needs a fix.

looks more ok now ;)

Some more typos to fix (there remains some others from before).

(I got some questions that are not answered yet but might be you're not done yet with the diff ;)

swh/core/db/db_utils.py
157
162
This revision is now accepted and ready to land.Feb 16 2022, 2:28 PM
douardda added inline comments.
swh/core/db/tests/test_cli.py
285

are you sure?

douardda added inline comments.
swh/core/db/tests/test_cli.py
284–288

not sure i prefer this suggestion in terms of readability though...

same, I'll keep my version version for now...

297–303

replaced by yaml.dump({...})

327

why?

because the advertised version of the code is 3 at this point in the test. The current_version variable is what get_current_version() will return thanks to the mocked version of the import_swhmodule function used in this test. (I've added a bit of comment to help).

swh/core/db/tests/test_db_utils.py
1–2

missing copyright notice btw

yes but that's an issue for D7062...

douardda marked 4 inline comments as done.

rebase + more fixes suggested by ardumont and vlorentz

Build is green

Patch application report for D7064 (id=26052)

Could not rebase; Attempt merge onto 3139d3344e...

Updating 3139d33..c17d64f
Fast-forward
 MANIFEST.in                                        |   2 +
 swh/core/cli/db.py                                 | 202 ++++++++++++++-
 swh/core/db/db_utils.py                            | 273 ++++++++++++++++++++-
 swh/core/db/sql/35-dbversion.sql                   |  18 ++
 swh/core/db/sql/36-dbmodule.sql                    |  15 ++
 swh/core/db/tests/conftest.py                      |  47 ++++
 .../tests/data/cli/{1-schema.sql => 30-schema.sql} |   2 +-
 .../db/tests/data/cli/{3-func.sql => 40-funcs.sql} |   0
 .../db/tests/data/cli/{4-data.sql => 50-data.sql}  |   2 +-
 .../db/tests/data/cli_new/0-superuser-init.sql     |   1 +
 swh/core/db/tests/data/cli_new/30-schema.sql       |   6 +
 swh/core/db/tests/data/cli_new/40-funcs.sql        |   6 +
 swh/core/db/tests/data/cli_new/50-data.sql         |   2 +
 swh/core/db/tests/data/cli_new/upgrades/001.sql    |   5 +
 swh/core/db/tests/data/cli_new/upgrades/002.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/003.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/004.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/005.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/006.sql    |   7 +
 swh/core/db/tests/test_cli.py                      | 216 ++++++++++++----
 swh/core/db/tests/test_db_utils.py                 | 174 +++++++++++++
 21 files changed, 930 insertions(+), 64 deletions(-)
 create mode 100644 swh/core/db/sql/35-dbversion.sql
 create mode 100644 swh/core/db/sql/36-dbmodule.sql
 rename swh/core/db/tests/data/cli/{1-schema.sql => 30-schema.sql} (87%)
 rename swh/core/db/tests/data/cli/{3-func.sql => 40-funcs.sql} (100%)
 rename swh/core/db/tests/data/cli/{4-data.sql => 50-data.sql} (73%)
 create mode 100644 swh/core/db/tests/data/cli_new/0-superuser-init.sql
 create mode 100644 swh/core/db/tests/data/cli_new/30-schema.sql
 create mode 100644 swh/core/db/tests/data/cli_new/40-funcs.sql
 create mode 100644 swh/core/db/tests/data/cli_new/50-data.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/001.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/002.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/003.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/004.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/005.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/006.sql
 create mode 100644 swh/core/db/tests/test_db_utils.py
Changes applied before test
commit c17d64f5884a148a3656c1b0993e3dcf30c4fb56
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 15:43:09 2022 +0100

    Add a 'swh db upgrade' command
    
    this new command allows to handle the database migration from the
    current version to the latest version advertized but the backend
    datastore.
    
    This feature is only working for "new style" swh.core.db based
    datastores.

commit 7e07b1f363269dc0bdcf3c1e7070ff0c0bacfdc6
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 14:51:43 2022 +0100

    Make 'swh db init' fill the dbversion table
    
    for this feature to work properly, the backend datastore package must
    provide the 'get_datastore' factory function in its root namespace and
    the datastore instance must provide a 'get_current_version()' method.
    
    This also means the canonical cli usage for the `swh db init` is now
    using the config file (via `--config-file` or `SWH_CONFIG_FILENAME`)
    instead of giving only the db cnx string (`--db-name`), so that the
    backend datastore class can be instanciated using the `get_datastore()`
    factory function.
    
    However a '--initial-version' cli option has been added to 'swh db init'
    to make it easier, especially for unit tests.

commit f8a07dfa0bf668905d66ad88b25b2e1f3f569177
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 17:54:01 2022 +0100

    Use a standard postgresql db fixture in test_cli
    
    instead of a custom 'test_db' (which did not truncate dbversion and
    origin tables, not sure what the intent was for this).

commit c4bd270c3000df52176dafbf5c7f16e283e918c8
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 15:02:51 2022 +0100

    Add support for dbversion and dbmodule handling in swh db init
    
    for now, managing the dbversion table (storing the db schema upgrade
    history) is partially the responsibility of the swh modules implementing
    a datastore (swh-storage etc.), and the actual swh module was not stored
    in the database.
    
    This adds support for both the dbversion and dbmodule tables management
    in swh.core.db.
    
    The `swh db init` command now creates both these tables and initialize
    their values if possible.
    For this, it introduces a new common "API" for swh.core.db based datastores:
    
    - a swh module implementing a datastore is expected to have a
      `get_datastore` function in its top namespace; this function is
      typically the actual `get_storage`, `get_scheduler` etc. functions
    - this factory function is expected to use the "postgresql" cls for the
      datastore based on swh.core.db,
    - the datastore instance (eg. instance of the swh.storage.postgresql.Storage
      class) is expected to have a `get_current_version()` method (returning
      an int).
    
    This revision also provides a new `swh db version` command displaying
    the current (code) version (if available), the db version (possibly the whole
    version history) and flavor (if any) for the datastore given as argument.
    
    It should be backward compatible with existing swh datastore modules (i.e.
    not yet adapted to this new API).

commit b1e449fd91ebcc403966a79fd62d449921fcf550
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 16:00:43 2022 +0100

    Refactor the mock_package_sql fixture to allow multiple sql script set
    
    will be used to implement several test scenarios in following commits.
    Also rename existing sql scripts with a "legit" numbering scheme.

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

Build is green

Patch application report for D7064 (id=26060)

Could not rebase; Attempt merge onto 3139d3344e...

Updating 3139d33..16e2fd4
Fast-forward
 MANIFEST.in                                        |   2 +
 swh/core/cli/db.py                                 | 202 ++++++++++++++-
 swh/core/db/db_utils.py                            | 273 ++++++++++++++++++++-
 swh/core/db/sql/35-dbversion.sql                   |  18 ++
 swh/core/db/sql/36-dbmodule.sql                    |  15 ++
 swh/core/db/tests/conftest.py                      |  47 ++++
 .../tests/data/cli/{1-schema.sql => 30-schema.sql} |   2 +-
 .../db/tests/data/cli/{3-func.sql => 40-funcs.sql} |   0
 .../db/tests/data/cli/{4-data.sql => 50-data.sql}  |   2 +-
 .../db/tests/data/cli_new/0-superuser-init.sql     |   1 +
 swh/core/db/tests/data/cli_new/30-schema.sql       |   6 +
 swh/core/db/tests/data/cli_new/40-funcs.sql        |   6 +
 swh/core/db/tests/data/cli_new/50-data.sql         |   2 +
 swh/core/db/tests/data/cli_new/upgrades/001.sql    |   5 +
 swh/core/db/tests/data/cli_new/upgrades/002.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/003.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/004.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/005.sql    |   4 +
 swh/core/db/tests/data/cli_new/upgrades/006.sql    |   7 +
 swh/core/db/tests/test_cli.py                      | 216 ++++++++++++----
 swh/core/db/tests/test_db_utils.py                 | 174 +++++++++++++
 21 files changed, 930 insertions(+), 64 deletions(-)
 create mode 100644 swh/core/db/sql/35-dbversion.sql
 create mode 100644 swh/core/db/sql/36-dbmodule.sql
 rename swh/core/db/tests/data/cli/{1-schema.sql => 30-schema.sql} (87%)
 rename swh/core/db/tests/data/cli/{3-func.sql => 40-funcs.sql} (100%)
 rename swh/core/db/tests/data/cli/{4-data.sql => 50-data.sql} (73%)
 create mode 100644 swh/core/db/tests/data/cli_new/0-superuser-init.sql
 create mode 100644 swh/core/db/tests/data/cli_new/30-schema.sql
 create mode 100644 swh/core/db/tests/data/cli_new/40-funcs.sql
 create mode 100644 swh/core/db/tests/data/cli_new/50-data.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/001.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/002.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/003.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/004.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/005.sql
 create mode 100644 swh/core/db/tests/data/cli_new/upgrades/006.sql
 create mode 100644 swh/core/db/tests/test_db_utils.py
Changes applied before test
commit 16e2fd4992efa439f074e11be4198cf5b0053aff
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 15:43:09 2022 +0100

    Add a 'swh db upgrade' command
    
    this new command allows to handle the database migration from the
    current version to the latest version advertized but the backend
    datastore.
    
    This feature is only working for "new style" swh.core.db based
    datastores.

commit 7e07b1f363269dc0bdcf3c1e7070ff0c0bacfdc6
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Feb 1 14:51:43 2022 +0100

    Make 'swh db init' fill the dbversion table
    
    for this feature to work properly, the backend datastore package must
    provide the 'get_datastore' factory function in its root namespace and
    the datastore instance must provide a 'get_current_version()' method.
    
    This also means the canonical cli usage for the `swh db init` is now
    using the config file (via `--config-file` or `SWH_CONFIG_FILENAME`)
    instead of giving only the db cnx string (`--db-name`), so that the
    backend datastore class can be instanciated using the `get_datastore()`
    factory function.
    
    However a '--initial-version' cli option has been added to 'swh db init'
    to make it easier, especially for unit tests.

commit f8a07dfa0bf668905d66ad88b25b2e1f3f569177
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 17:54:01 2022 +0100

    Use a standard postgresql db fixture in test_cli
    
    instead of a custom 'test_db' (which did not truncate dbversion and
    origin tables, not sure what the intent was for this).

commit c4bd270c3000df52176dafbf5c7f16e283e918c8
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 15:02:51 2022 +0100

    Add support for dbversion and dbmodule handling in swh db init
    
    for now, managing the dbversion table (storing the db schema upgrade
    history) is partially the responsibility of the swh modules implementing
    a datastore (swh-storage etc.), and the actual swh module was not stored
    in the database.
    
    This adds support for both the dbversion and dbmodule tables management
    in swh.core.db.
    
    The `swh db init` command now creates both these tables and initialize
    their values if possible.
    For this, it introduces a new common "API" for swh.core.db based datastores:
    
    - a swh module implementing a datastore is expected to have a
      `get_datastore` function in its top namespace; this function is
      typically the actual `get_storage`, `get_scheduler` etc. functions
    - this factory function is expected to use the "postgresql" cls for the
      datastore based on swh.core.db,
    - the datastore instance (eg. instance of the swh.storage.postgresql.Storage
      class) is expected to have a `get_current_version()` method (returning
      an int).
    
    This revision also provides a new `swh db version` command displaying
    the current (code) version (if available), the db version (possibly the whole
    version history) and flavor (if any) for the datastore given as argument.
    
    It should be backward compatible with existing swh datastore modules (i.e.
    not yet adapted to this new API).

commit b1e449fd91ebcc403966a79fd62d449921fcf550
Author: David Douard <david.douard@sdfa3.org>
Date:   Fri Jan 28 16:00:43 2022 +0100

    Refactor the mock_package_sql fixture to allow multiple sql script set
    
    will be used to implement several test scenarios in following commits.
    Also rename existing sql scripts with a "legit" numbering scheme.

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

This revision was automatically updated to reflect the committed changes.