Return AiiDA IntegrityErrors instead of Django/SQLAlchemy ones

[giovannipizzi]

E.g. now in the tests we have

def test_uuid_uniquess(self):
        """
        A uniqueness constraint on the UUID column of the Node model should prevent multiple nodes with identical UUID
        """
        from django.db import IntegrityError as DjIntegrityError
        from sqlalchemy.exc import IntegrityError as SqlaIntegrityError

        a = Data()
        b = Data()
        b._dbnode.uuid = a.uuid
        a.store()

        with self.assertRaises((DjIntegrityError, SqlaIntegrityError)):
            b.store()

[sphuber]

In aiida-core==2.0 the new syntax for recreating this problem is:

a = Data()
b = Data()
b.backend_entity.bare_model.uuid = a.uuid
a.store()
b.store()

which will raise:

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-3-5eb8363bee6b> in <module>
----> 1 b._dbnode.uuid = a.uuid

AttributeError: 'Data' object has no attribute '_dbnode'

In [4]: b.backend_entity.dbmodel.uuid = a.uuid

In [5]: a.store()
Out[5]: <Data: uuid: 40c2b843-f6c8-4706-9de3-7c60b8462e2e (pk: 2)>

In [6]: b.store()
---------------------------------------------------------------------------
UniqueViolation                           Traceback (most recent call last)
~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1801                 if not evt_handled:
-> 1802                     self.dialect.do_execute(
   1803                         cursor, statement, parameters, context

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    731     def do_execute(self, cursor, statement, parameters, context=None):
--> 732         cursor.execute(statement, parameters)
    733 

UniqueViolation: duplicate key value violates unique constraint "db_dbnode_uuid_62e0bf98_uniq"
DETAIL:  Key (uuid)=(40c2b843-f6c8-4706-9de3-7c60b8462e2e) already exists.


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

IntegrityError                            Traceback (most recent call last)
<ipython-input-6-d0c69de7e7df> in <module>
----> 1 b.store()

~/code/aiida/env/dev/aiida-core/aiida/orm/nodes/node.py in store(self, with_transaction)
    714                 self._store_from_cache(same_node, with_transaction=with_transaction)
    715             else:
--> 716                 self._store(with_transaction=with_transaction, clean=True)
    717 
    718             if self.backend.autogroup.is_to_be_grouped(self):

~/code/aiida/env/dev/aiida-core/aiida/orm/nodes/node.py in _store(self, with_transaction, clean)
    742 
    743         links = self._incoming_cache
--> 744         self._backend_entity.store(links, with_transaction=with_transaction, clean=clean)
    745 
    746         self._incoming_cache = []

~/code/aiida/env/dev/aiida-core/aiida/orm/implementation/sqlalchemy/nodes.py in store(self, links, with_transaction, clean)
    225         if with_transaction:
    226             try:
--> 227                 session.commit()
    228             except SQLAlchemyError:
    229                 session.rollback()

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/orm/session.py in commit(self)
   1429                 raise sa_exc.InvalidRequestError("No transaction is begun.")
   1430 
-> 1431         self._transaction.commit(_to_root=self.future)
   1432 
   1433     def prepare(self):

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/orm/session.py in commit(self, _to_root)
    827         self._assert_active(prepared_ok=True)
    828         if self._state is not PREPARED:
--> 829             self._prepare_impl()
    830 
    831         if self._parent is None or self.nested:

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/orm/session.py in _prepare_impl(self)
    806                 if self.session._is_clean():
    807                     break
--> 808                 self.session.flush()
    809             else:
    810                 raise exc.FlushError(

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/orm/session.py in flush(self, objects)
   3361         try:
   3362             self._flushing = True
-> 3363             self._flush(objects)
   3364         finally:
   3365             self._flushing = False

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/orm/session.py in _flush(self, objects)
   3501         except:
   3502             with util.safe_reraise():
-> 3503                 transaction.rollback(_capture_exception=True)
   3504 
   3505     def bulk_save_objects(

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback)
     68             self._exc_info = None  # remove potential circular references
     69             if not self.warn_only:
---> 70                 compat.raise_(
     71                     exc_value,
     72                     with_traceback=exc_tb,

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
    205 
    206         try:
--> 207             raise exception
    208         finally:
    209             # credit to

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/orm/session.py in _flush(self, objects)
   3461             self._warn_on_events = True
   3462             try:
-> 3463                 flush_context.execute()
   3464             finally:
   3465                 self._warn_on_events = False

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py in execute(self)
    454         else:
    455             for rec in topological.sort(self.dependencies, postsort_actions):
--> 456                 rec.execute(self)
    457 
    458     def finalize_flush_changes(self):

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py in execute(self, uow)
    628     @util.preload_module("sqlalchemy.orm.persistence")
    629     def execute(self, uow):
--> 630         util.preloaded.orm_persistence.save_obj(
    631             self.mapper,
    632             uow.states_for_mapper_hierarchy(self.mapper, False, False),

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py in save_obj(base_mapper, states, uowtransaction, single)
    242         )
    243 
--> 244         _emit_insert_statements(
    245             base_mapper,
    246             uowtransaction,

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py in _emit_insert_statements(base_mapper, uowtransaction, mapper, table, insert, bookkeeping)
   1219                         )
   1220                     else:
-> 1221                         result = connection._execute_20(
   1222                             statement,
   1223                             params,

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/engine/base.py in _execute_20(self, statement, parameters, execution_options)
   1612             )
   1613         else:
-> 1614             return meth(self, args_10style, kwargs_10style, execution_options)
   1615 
   1616     def exec_driver_sql(

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params, execution_options, _force)
    323     ):
    324         if _force or self.supports_execution:
--> 325             return connection._execute_clauseelement(
    326                 self, multiparams, params, execution_options
    327             )

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params, execution_options)
   1479             linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
   1480         )
-> 1481         ret = self._execute_context(
   1482             dialect,
   1483             dialect.execution_ctx_cls._init_compiled,

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1843 
   1844         except BaseException as e:
-> 1845             self._handle_dbapi_exception(
   1846                 e, statement, parameters, cursor, context
   1847             )

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   2024                 util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   2025             elif should_wrap:
-> 2026                 util.raise_(
   2027                     sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   2028                 )

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
    205 
    206         try:
--> 207             raise exception
    208         finally:
    209             # credit to

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1800                             break
   1801                 if not evt_handled:
-> 1802                     self.dialect.do_execute(
   1803                         cursor, statement, parameters, context
   1804                     )

~/.virtualenvs/aiida_dev/lib/python3.9/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    730 
    731     def do_execute(self, cursor, statement, parameters, context=None):
--> 732         cursor.execute(statement, parameters)
    733 
    734     def do_execute_no_params(self, cursor, statement, context=None):

IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "db_dbnode_uuid_62e0bf98_uniq"
DETAIL:  Key (uuid)=(40c2b843-f6c8-4706-9de3-7c60b8462e2e) already exists.

[SQL: INSERT INTO db_dbnode (uuid, node_type, process_type, label, description, ctime, mtime, attributes, extras, repository_metadata, dbcomputer_id, user_id) VALUES (%(uuid)s, %(node_type)s, %(process_type)s, %(label)s, %(description)s, %(ctime)s, %(mtime)s, %(attributes)s, %(extras)s, %(repository_metadata)s, %(dbcomputer_id)s, %(user_id)s) RETURNING db_dbnode.id]
[parameters: {'uuid': '40c2b843-f6c8-4706-9de3-7c60b8462e2e', 'node_type': 'data.Data.', 'process_type': None, 'label': '', 'description': '', 'ctime': datetime.datetime(2022, 2, 17, 9, 50, 8, 620550, tzinfo=<UTC>), 'mtime': datetime.datetime(2022, 2, 17, 9, 50, 37, 509953, tzinfo=<UTC>), 'attributes': '{}', 'extras': '{}', 'repository_metadata': '{}', 'dbcomputer_id': None, 'user_id': 1}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

[chrisjsewell]

@sphuber, after #5358, there's a new new syntax lol

[sphuber]

Updated :+1: