Upsert statement cannot be tested with an sqlite database

[eboileau]

A clear and concise description of what the bug is.

The upsert statement cannot be tested with an sqlite/in-memory database, so e.g. test_setup.py cannot test the setup. This can be reproduced with

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from scimodom.database.database import init

engine = create_engine("sqlite:///:memory:")
session = sessionmaker(autocommit=False, autoflush=False, bind=engine)
init(engine, lambda: session)

from scimodom.services.setup import SetupService
setup = SetupService(session())
setup.upsert_all() # <- fails with sqlite due to upsert

Output or error messages.

Traceback (most recent call last):
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 139, in _compiler_dispatch
    meth = getter(visitor)
AttributeError: 'SQLiteCompiler' object has no attribute 'visit_on_duplicate_key_update'

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

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/eboileau/prj/RMapDFGTRR319/repositories/scimodom/server/src/scimodom/services/setup.py", line 77, in upsert_all
    self.bulk_upsert(model, table)
  File "/home/eboileau/prj/RMapDFGTRR319/repositories/scimodom/server/src/scimodom/services/setup.py", line 69, in bulk_upsert
    self._session.execute(stmt)
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2246, in execute
    return self._execute_internal(
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2141, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/orm/bulk_persistence.py", line 1272, in orm_execute_statement
    result = conn.execute(
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1412, in execute
    return meth(
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 483, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1627, in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 684, in _compile_w_cache
    compiled_sql = self._compiler(
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 288, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 1425, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 866, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 911, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/sql/annotation.py", line 351, in _compiler_dispatch
    return self.__element.__class__._compiler_dispatch(
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 143, in _compiler_dispatch
    return meth(self, **kw)  # type: ignore  # noqa: E501
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 5930, in visit_insert
    post_values_clause = self.process(
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 911, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 141, in _compiler_dispatch
    return visitor.visit_unsupported_compilation(self, err, **kw)  # type: ignore  # noqa: E501
  File "/home/eboileau/.venv/scimodom-server/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 898, in visit_unsupported_compilation
    raise exc.UnsupportedCompilationError(self, type(element)) from err
sqlalchemy.exc.UnsupportedCompilationError: Compiler <sqlalchemy.dialects.sqlite.base.SQLiteCompiler object at 0x7f4ed81d59d0> can't render element of type <class 'sqlalchemy.dialects.mysql.dml.OnDuplicateClause'> (Background on this error at: https://sqlalche.me/e/20/l7de)

Additional context

No response

What browser were you using?

Other

What version of Sci-ModoM were you using?

dev