Problem:
Since #65 It is necessary in some cases to explicitly name constraints as explained in #69. We need to document that. We should also say that the name should be less than 64 characters (see postgresql and mysql limitations) and that it should be as close as possible to the naming convention defined in shared.py

Note: the naming is needed both for models and alembic scripts.

Also the tests should be made with mysql. Postgresql seems to truncate the name by default so everything seems fine but it isn't.

https://travis-ci.org/inveniosoftware/invenio-db/jobs/560026691

A combination of the SQLAlchemy-Continnuum versioning support and initialising multiple Flask applications seems to the the source of these warnings:

...sqlalchemy/ext/declarative/clsregistry.py:120: SAWarning: This declarative base already contains a class with the same class name and module name as flask_sqlalchemy.PageListVersion, and will be replaced in the string-lookup table.
  item.__name__
...sqlalchemy/ext/declarative/clsregistry.py:120: SAWarning: This declarative base already contains a class with the same class name and module name as flask_sqlalchemy.RecordMetadataVersion, and will be replaced in the string-lookup table.
  item.__name__
...sqlalchemy/ext/declarative/clsregistry.py:120: SAWarning: This declarative base already contains a class with the same class name and module name as flask_sqlalchemy.PageVersion, and will be replaced in the string-lookup table.
  item.__name__
...sqlalchemy/orm/properties.py:194: SAWarning: On mapper Mapper|PageVersion|pages_page_version, primary key column 'pages_page_version.transaction_id' is being combined with distinct primary key column 'pages_page_version.transaction_id' in attribute 'transaction_id'.  Use explicit properties to give each column its own mapped attribute name.
  self.columns[0], self.key))
...sqlalchemy/orm/properties.py:194: SAWarning: On mapper Mapper|PageListVersion|pages_pagelist_version, primary key column 'pages_pagelist_version.transaction_id' is being combined with distinct primary key column 'pages_pagelist_version.transaction_id' in attribute 'transaction_id'.  Use explicit properties to give each column its own mapped attribute name.
  self.columns[0], self.key))
...sqlalchemy/orm/properties.py:194: SAWarning: On mapper Mapper|RecordMetadataVersion|records_metadata_version, primary key column 'records_metadata_version.transaction_id' is being combined with distinct primary key column 'records_metadata_version.transaction_id' in attribute 'transaction_id'.  Use explicit properties to give each column its own mapped attribute name.
  self.columns[0], self.key))

The warnings are issued when Invenio-DB is initialized here.

What happens is:

1. Flask UI app is created and Invenio-DB is initialised first time (above mentioned initialisation step is not reached).
2. Flask REST app is created (and Invenio-DB is initialised a second time (now above initialisation step is reached).

Above is fully normal when running e.g. development server or UI/API on the same Python interpreter.

The reason for above initialisation step was because sometimes loading order would prevent the versioning tables from being created (see 336a7ee - this was quite a pain to debug).

cc @jacquerie

Goal is to ensure that Invenio-App-ILS does not have unnecessary warnings when you start the development server.

• configurable User model
• switch on/off the feature

psycopg2 package's name wil be changed:

UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.

Undo #111 when has been fixed kvesteri/sqlalchemy-continuum#188

After upgrading pytest-invenio to version 1.4.0 and applying changes, tests started failing.
The issue is the pytest argument --doctest-modules in the file pytest.ini but the cause is not clear.

The 3 tests that are failing are the ones using the decorator:
@patch('pkg_resources.iter_entry_points', _mock_entry_points)

For the time being, the argument --doctest-modules has been removed.

• E.g. remove "This is an experimental developer preview release."

As spotted by @dinosk in this troubleshooting, the call to db.drop_all() will NOT delete the alembic table alembic_version.

This can lead to several unpredictable issues.
The drop_all() method should drop ALL tables, and not left something behind.
A workaround has been provided with the method invenio_db.utils.drop_alembic_version_table().

Proposed solution
Override the SQLAlchemy db.drop_all() method to (pseudo-code):

def def drop_all():
    super()
    drop_alembic_version_table()

WARNING: this modification can potentially break other modules' tests. Moreover, whenever the function drop_alembic_version_table() is used after a drop_all(), it should be cleaned up.

In relation to #195 in invenio-accounts, after creating the database with:
flask alembic upgrade heads

dropping the tables: flask db drop leaves these two in the db:

+----------+-----------------+--------+----------+
| Schema   | Name            | Type   | Owner    |
|----------+-----------------+--------+----------|
| public   | alembic_version | table  | postgres |
| public   | transaction     | table  | postgres |
+----------+-----------------+--------+----------+

For the transaction table, alembic downgrade would delete it fine but it is not currently found in this loop <- this is wrong, it is registered but can't be dropped because of the transaction_id_seq depending on it.

With the 0.8 version of pymysql, travis builds that use MySQL will start failing, for example this build of invenio-files-rest:
https://travis-ci.org/switowski/invenio-files-rest/jobs/346369793

The enigmatic <Result StatementError('(exceptions.ValueError) bytes is not a 16-char string',)>.exit_code error seems to be caused by an update of pymysql, that is introducing a backward incompatible change: PyMySQL/PyMySQL@4ff1bbc

We can restore our previous behaviour by setting the binary_prefix = True in the connection parameters of the pymysql.
I will submit a PR shortly.

https://invenio-db.readthedocs.io/en/latest/examplesapp.html

Describe the bug

SQLAlchemy-Continuum has a new version since few days. Using the last version (1.4.0), executing the tests in our invenio instance we got the following error:

self = <sqlalchemy_continuum.manager.VersioningManager object at 0x1082c9460>, conn = <sqlalchemy.engine.base.Connection object at 0x10c522280>, clauseelement = 'BEGIN', multiparams = [], params = {}, execution_options = immutabledict({})

    def track_association_operations(
        self, conn, clauseelement, multiparams, params, execution_options,
    ):

        if (
            not self.options['versioning'] and
            not self.options['native_versioning']
        ):
            return

>       if clauseelement.is_insert:
E       AttributeError: 'str' object has no attribute 'is_insert'

Using SQLAlchemy-Continuum == 1.3.15, everything works nicely.

This issue stems from https://github.com/inveniosoftware/invenio-cli potentially re-running invenio db destroy multiple times under the covers and not wanting to throw an exception when the DB is already destroyed.

Like Ansible, invenio-cli's goal (and by extension this cli commands' goal) is to get to a state rather than perform a certain action.

Make a new patch-level release

Problem
In the last commit a fix to the drop command was applied to handle a cascade drop error from transaction_id_seq.
This is not addressed when using db.drop_all(), and the tests in some modules (so far noticed in invenio-records, invenio-pidstore) will fail in test_alembic.

Options

• Create util function to replace drop_all().
• Modify the creation of the problematic sequence in the alembic recipe & provide util functions to
  create and drop the alembic_version table for the tests.

PR #84 modifies the creation of the sequence as suggested here to avoid the error when dropping it.

Package version (if known): October release of invenio-app-rdm : 0.16.6 (invenio-db 1.0.7)

Describe the bug

invenio-cli containerize --pre will install alpha releases and therefore pickup sqlalchemy 1.4.0b1 which is "unofficially incompatible" with our other dependency sqlalchemy-utils 0.35.0. By "unofficially incompatible", we mean that pipenv / pip do allow installation of both libraries without issue, but their code is incompatible. The incompatibility is here:

sqlalchemy-utils imports from sqlalchemy _literal_as_text which doesn't exist in sqlalchemy 1.4.0b1 (and there is no breaking change, because sqlalchemy-utils was importing a private function - that's what you get). That internal function was removed here for a class-system: sqlalchemy/sqlalchemy@f07e050

Potential solutions

1- Short-term: cap sqlalchemy in invenio-db to <1.4.0b1
2- Longer-term when 1.4.0 is out: Investigate and resolve why we are keeping sqlalchemy-util at 0.35.0 and upgrade it

• Build error looks related to change in SQLAlchemy-Utils for the EncryptedType.

In Invenio-OAuthClient/OAuth2Server the same issue was recently fixed:
from sqlalchemy_utils.types.encrypted import EncryptedType -> from sqlalchemy_utils import EncryptedType

The following cookiecutter change:

inveniosoftware/cookiecutter-invenio-module#98

should be propagated to this Invenio module.

Namely, in run-tests.sh, the sphinx for doctests is invoked after pytest run:

$ tail -3 ./\{\{\ cookiecutter.project_shortname\ \}\}/run-tests.sh
sphinx-build -qnNW docs docs/_build/html && python setup.py test && sphinx-build -qnNW -b doctest docs docs/_build/doctest

This sometimes led to problems on Travis CI with the second sphinx-build run due
to "disappearing" dependencies after the example application was tested.

A solution that worked for invenio-marc21 (see
inveniosoftware/invenio-marc21#49 (comment))
and that was integrated in cookiecutter (see
inveniosoftware/cookiecutter-invenio-module#98) was to
run doctest execution in pytest, removing the second sphinx-build invocation.

This both solved Travis CI build failures and simplified test suite execution.

Note that this change may necessitate to amend the code tests etc so that things
would be executed with the Flask application context (see
inveniosoftware/invenio-marc21@09e98fc).

General

Flask 2.2 deprecated the variable _app_ctx_stack (and a lot of other stuff). Flask 2.3 removed most of the things 2.2 deprecated. _app_ctx_stack was heavily used in a lot of packages so also in the SQLAlchemy packages. Only the newest versions remove this usage so all dependencies have to be updated.

The deprecation warning looks like:

env/lib/python3.9/site-packages/flask_sqlalchemy/__init__.py:302: DeprecationWarning: '_app_ctx_stack' is deprecated and will be removed in Flask 2.3. Use 'g' to store data, or 'app_ctx' to access the current context.
  queries = _app_ctx_stack.top.sqlalchemy_queries

Problems

• With SQLAlchemy 2.0 the definition of a Session changed slightly

NOTES

• pallets-eco/flask-alembic#3 how could that be merged and released?

1. Add this documentation to the module in a similar way to how the optimistic concurrency control was done in Invenio-Records.

2. Add a cross-reference to the "optimistic concurrency control" in Invenio-Records

Package version (if known): latest

Reported by Werner for Uni Münster.

Describe the bug

Log files show that the server closed the connection unexpectedly (see traceback).

The application is running in a k8s cluster, and it could likely be associated with the connection pooling. Here's a possible solution is described using the pool_pre_ping.

See

• https://stackoverflow.com/questions/55457069/how-to-fix-operationalerror-psycopg2-operationalerror-server-closed-the-conn
• https://docs.sqlalchemy.org/en/14/core/pooling.html#dealing-with-disconnects

Steps to reproduce

One possible approach would be to boot up e.g. an InvenioRDM. Browse a couple of pages. Then, restart the database server.

Additional context

[2021-03-05 12:09:14,264] ERROR in app: Exception on /records [GET]
Traceback (most recent call last):
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
psycopg2.OperationalError: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask_restful/__init__.py", line 272, in error_router
    return original_handler(e)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/views.py", line 89, in view
    return self.dispatch_request(*args, **kwargs)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask_resources/views/base.py", line 51, in dispatch_request
    return view(self, *args, **kwargs)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask_resources/context.py", line 71, in inner
    return f(self, *args, **kwargs)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask_resources/parsers/base.py", line 44, in inner
    return f(self, *args, **kwargs)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask_resources/content_negotiation.py", line 106, in inner
    return f(self, *args, **kwargs)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask_resources/responses.py", line 30, in wrapper
    return f(self, *args, **kwargs)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask_resources/context.py", line 87, in inner
    return f(*args)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask_resources/parsers/base.py", line 44, in inner
    return f(self, *args, **kwargs)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask_resources/loaders.py", line 59, in wrapper
    return f(self, *args, **kwargs)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask/views.py", line 163, in dispatch_request
    return meth(*args, **kwargs)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask_resources/views/collections.py", line 43, in get
    *self.resource.search(*args, **kwargs)
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/invenio_records_resources/resources/records/resource.py", line 51, in search
    es_preference=self._get_es_preference()
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/invenio_records_resources/services/records/service.py", line 167, in search
    self.require_permission(identity, 'search')
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/invenio_records_resources/services/base/service.py", line 44, in require_permission
    if not self.permission_policy(action_name, **kwargs).allows(identity):
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/flask_principal.py", line 333, in allows
    if self.needs and not self.needs.intersection(identity.provides):
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/invenio_records_permissions/policies/base.py", line 89, in needs
    self._load_permissions()  # self.explicit_needs is used here
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/invenio_access/permissions.py", line 169, in _load_permissions
    result.update(self._expand_action(need))
  File "/opt/rh/rh-python36/root/usr/lib/python3.6/site-packages/invenio_access/permissions.py", line 191, in _expand_action
    actionsusers = ActionUsers.query_by_action(explicit_action).all()
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/sqlalchemy/orm/query.py", line 3373, in all
    return list(self)
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/sqlalchemy/orm/query.py", line 3535, in __iter__
    return self._execute_and_instances(context)
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/sqlalchemy/orm/query.py", line 3560, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1130, in _execute_clauseelement
    distilled_params,
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1317, in _execute_context
    e, statement, parameters, cursor, context
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1511, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

[SQL: SELECT access_actionsusers.id AS access_actionsusers_id, access_actionsusers.action AS access_actionsusers_action, access_actionsusers.exclude AS access_actionsusers_exclude, access_actionsusers.argument AS access_actionsusers_argument, access_actionsusers.user_id AS access_actionsusers_user_id 
FROM access_actionsusers 
WHERE access_actionsusers.action = %(action_1)s AND access_actionsusers.argument IS NULL]
[parameters: {'action_1': 'superuser-access'}]
(Background on this error at: http://sqlalche.me/e/13/e3q8)
[pid: 15|app: 0|req: 128/309] 10.51.216.206 () {56 vars in 797 bytes} [Fri Mar  5 12:09:14 2021] GET /api/records => generated 182 bytes in 20 msecs (HTTP/1.1 500) 13 headers in 644 bytes (1 switches on core 3)

Currently SQLAlchemy has JSONB support for PostgreSQL:
http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB

However, it doesn't seem like it will go into JSONType in SQLAlchemy-Utils due to difficulties in making it behave nicely across DBs:
kvesteri/sqlalchemy-utils#125

For Invenio-Records we could likely get along with using JSONB when on PostgreSQL (and stay with text no the other platforms), as all we basically do is get and put the entire JSON so comparators is less of an issue.

This is perhaps a non issue but this:

https://github.com/inveniosoftware/invenio-db/blob/master/invenio_db/core.py#L58 will request a sqlite database at something like "./instance/testapp.db".

will cause the following error at the first testing of a new module:

OperationalError: (sqlite3.OperationalError) unable to open database file

becase the instance directory is not in the cookiecutted by default.

It seems that sqlite cannot create an extra directory, but it can create the testapp.db file once the directory instance is in place.
So this is either a ticket for cookiecutter template to include the instance dir, or (which I believe to be better) changing the default path to the root of the repo and add it to the .gitignore.

The documentation should include minimal documentation on how SQLAlchemy-Continuum have been integrated and can be used.

Likewise, the integration of SQLAlchemy-Utils should be documented.

SQLAlchemy + PostgreSQL does not handle Werkzeug's LocalProxy when used in queries.
E.g. this PR add a call to str around the key in record.files[str(key) in order to ensure the a string is passed down instead of a LocalProxy. This should however not be necessary and was already fixed in Invenio 2.0 - most likely using the feature:
http://docs.sqlalchemy.org/en/latest/core/compiler.html#changing-compilation-of-types

Scenario:

• use SQLite
• run something like this:

class Demo(db.Model):
    __tablename__ = 'demo'
    pk = db.Column(sa.Integer, primary_key=True)

... create the application and the tables ...

with app.app_context():
    with db.session.begin_nested():
        d1 = Demo()
        d1.pk = 1
        db.session.add(d1)
    db.session.rollback()
with app.app_context():
    res = Demo.query.all()
    print res

You will see that the result is read even though the transaction has been rollbacked.

Reason:
We prevented SQLAlchemy from handling transaction scope automatically by changing the isolation_level as described here http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#serializable-isolation-savepoints-transactional-ddl
However we did not send our own BEGIN request.
Because of this, the savepoints are really committed.

The package contains examples/app.py example application, but there is no test
for it in tests/test_example_app.py. It should be added. See existing examples:

• https://github.com/inveniosoftware/invenio-records/blob/master/tests/test_example_app.py
• https://github.com/inveniosoftware/invenio-marc21/blob/master/tests/test_example_app.py

This link is broken:

The constraint naming conventions introduced in #65 seems to be generating names without taking MySQL/PostgreSQL limitations into account.

It also seems like that it's not possible to override the naming convention in order to set a name within the limits. The causes problems for e.g. oauthclient/oauth2server and other modules where constraint names generated are too long.

I've tried to produce a minimal breaking PR that demonstrates the problem.

Best would be if it was possible to just override the name for a given constraint if the name generated is too long. If that's not possible we may need to remove the naming conventions again, and force that models explicitly specify a constraint name.

MySQL version 8 fails on authenticating through invenio (via cmd it works) most likely som storage/codification issue.

• check existing API functionality
• add missing important API functionality
• check API function signatures and parameters
• enhance API docstrings (param, returns, raises, versionadded)
• plug API functions to existing docs

Python 2: https://travis-ci.org/github/inveniosoftware/invenio-db/jobs/664519858#L325

• introduce new documentation structure inveniosoftware/cookiecutter-invenio-module#56
• document user perspective
• document signals (if not done)
• document example app (if not done)

Steps

Run the tests:

$ env DB=mysql MYSQL_VERSION=MYSQL_8_LATEST SQLALCHEMY_DATABASE_URI=mysql+pymysql://invenio:invenio@localhost:3306/invenio ./run-tests.sh

The test tests_db.py::test_db_create_alembic_upgrade while block on trying to run invenio db drop -v --yes-i-know.

Tests output

    def test_db_create_alembic_upgrade(app, db):
        """Test that 'db create/drop' and 'alembic create' are compatible.

        It also checks that "alembic_version" table is processed properly
        as it is normally created by alembic and not by sqlalchemy.
        """
        app.config['DB_VERSIONING'] = True
        ext = InvenioDB(app, entry_point_group=None, db=db,
                        versioning_manager=VersioningManager())
        with app.app_context():
            try:
                if db.engine.name == 'sqlite':
                    raise pytest.skip('Upgrades are not supported on SQLite.')
                db.drop_all()
                runner = CliRunner()
                script_info = ScriptInfo(create_app=lambda info: app)
                # Check that 'db create' creates the same schema as
                # 'alembic upgrade'.
                result = runner.invoke(db_cmd, ['create', '-v'], obj=script_info)
                assert result.exit_code == 0
                assert db.engine.has_table('transaction')
                assert ext.alembic.migration_context._has_version_table()
                # Note that compare_metadata does not detect additional sequences
                # and constraints.
                assert not ext.alembic.compare_metadata()
                ext.alembic.upgrade()
                assert db.engine.has_table('transaction')

                ext.alembic.downgrade(target='96e796392533')
                assert db.engine.table_names() == ['alembic_version']

                # Check that 'db drop' removes all tables, including
                # 'alembic_version'.
                ext.alembic.upgrade()
                result = runner.invoke(db_cmd, ['drop', '-v', '--yes-i-know'],
                                       obj=script_info)
>               assert result.exit_code == 0
E               assert 1 == 0
E                +  where 1 = <Result SystemExit(1)>.exit_code

tests/test_db.py:381: AssertionError

It'd be good to rename example/app.py to examples/app.py.

Invenio-DB is the only Invenio 3 package that uses singular instead of plural.

The default collation in MySQL 5.6 (though this might apply for newer versions as well), is utf8_general_ci which is case-insensitive. This means that the following can happen:

class Identifier(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    value = db.Column(db.String(255), unique=True)
...

id1 = Identifier(value='ABC')
db.session.add(id1)
db.session.commit()
assert id1.id == 1

id2 = Identifier(value='abc')
db.session.add(id2)
db.session.commit()
# ...DB error for unique constraint violation...

fetched_id = Identifier.query.filter_by(value='aBc').one()
assert fetched_id == id1
assert fetched_id.id == 1

This is probably causing problems with many assumptions that we as developers make throughout the Invenio codebase (especially on tables in e.g. invenio-pidstore).

A more correct collation + charset would come from creating the database with:

CREATE DATABASE invenio CHARACTER SET utf8 COLLATE utf8_bin;

As we discussed during IDF we should force naming conventions for PK, FK, ... Please read: http://docs.sqlalchemy.org/en/latest/core/constraints.html#configuring-constraint-naming-conventions

These lines needs a test case.

Description of bug:

• sa.orm.configure_mappers() sends the after_configured signal only if there are new model classes
• SQLAlchemy-Continuum relies on after_configured signal to call configure_versioned_classes() which builds the model version classes.
• If all models have been loaded and sa.orm.configure_mappers() has been called prior to SQLAlchemy-Continuum being initialised, then the versioned classes will not be created without this extra call

If kwargs is provided and app is not, then kwargs is not taken into consideration in init_app
invenio-db/invenio_db/core.py

Add it as a test matrix, so that we test both v9.6 and v10

https://docs.travis-ci.com/user/database-setup/#using-a-different-postgresql-version

db init
db create
db destroy
db drop
alembic upgrade
alembic heads
alembic revision ....

Basic things about how to create a migration for an Invenio module

When installing Invenio v3.0 on PostgreSQL, and when the database already exists, the db init command leads to troubles, see Invenio's kickstart scripts. Investigate this.

Currently we're pinning SQLAlchemy <1.4, sqlalchemy-utils<0.36, flask-sqlalchemy<2.5.

All of these pins are related. Currently, e.g. sqlalchemy-utils is not compatible with SQLAlchemy v1.4, while Flask-SQLAlchemy v2.5 was just release and is compatible with v1.4. There's a PR for SQLAlchemy-Utils - see the issue: kvesteri/sqlalchemy-utils#505