Migration fails if user has made a table called "temp"

[leendertvanwolfswinkel]

For example this one:

[Sibbergrubbe VKV T25 Gebiedsbreed.zip] (https://api.zenhub.com/attachedFiles/eyJfcmFpbHMiOnsibWVzc2FnZSI6IkJBaHBBMmZZQmc9PSIsImV4cCI6bnVsbCwicHVyIjoiYmxvYl9pZCJ9fQ==--1b6caedfbde88ed625f49558180aa474d7dc5814/Sibbergrubbe%20VKV%20T25%20Gebiedsbreed.zip)

Probably better to name the table e.g. temp_{generated uuid} or something?

>>> migrate_spatialite(r"C:\Users\leendert.vanwolfswin\Documents\migration_checker\zipped\Sibbergrubbe\Sibbergrubbe VKV T25 Gebiedsbreed.sqlite"
... )
updateTableTriggers: "table "idx__alembic_tmp_v2_cross_section_location_the_geom" already exists"
Traceback (most recent call last):
  File "C:\Users\leendert.vanwolfswin\Documents\migration_checker\migration_checker.py", line 38, in migrate_spatialite
    schema.validate_schema()
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\threedi_schema\application\schema.py", line 168, in validate_schema
    raise MigrationMissingError(
threedi_schema.application.errors.MigrationMissingError: This tool requires at least schema version 224. Current version: 219.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\sqlalchemy\engine\base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\sqlalchemy\engine\default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: table temp already exists

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

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Users\leendert.vanwolfswin\Documents\migration_checker\migration_checker.py", line 43, in migrate_spatialite
    schema.upgrade(backup=False, upgrade_spatialite_version=True)
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\threedi_schema\application\schema.py", line 146, in upgrade
    _upgrade_database(self.db, revision=revision, unsafe=False)
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\threedi_schema\application\schema.py", line 48, in _upgrade_database
    alembic_command.upgrade(config, revision)
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\alembic\command.py", line 406, in upgrade
    script.run_env()
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\alembic\script\base.py", line 582, in run_env
    util.load_python_file(self.dir, "env.py")
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\alembic\util\pyfiles.py", line 95, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\alembic\util\pyfiles.py", line 113, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap_external>", line 940, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\threedi_schema\migrations\env.py", line 60, in <module>
    run_migrations_online()
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\threedi_schema\migrations\env.py", line 54, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\alembic\runtime\environment.py", line 946, in run_migrations
    self.get_context().run_migrations(**kw)
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\alembic\runtime\migration.py", line 628, in run_migrations
    step.migration_fn(**kw)
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\threedi_schema\migrations\versions\0222_upgrade_db_settings.py", line 384, in upgrade
    remove_columns_from_copied_tables(table, columns)
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\threedi_schema\migrations\versions\0222_upgrade_db_settings.py", line 332, in remove_columns_from_copied_tables
    op.execute(sa.text(f"CREATE TABLE temp ({','.join(cols)});"))
  File "<string>", line 8, in execute
  File "<string>", line 3, in execute
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\alembic\operations\ops.py", line 2537, in execute
    return operations.invoke(op)
           ^^^^^^^^^^^^^^^^^^^^^
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\alembic\operations\base.py", line 442, in invoke
    return fn(self, operation)
           ^^^^^^^^^^^^^^^^^^^
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\alembic\operations\toimpl.py", line 224, in execute_sql
    operations.migration_context.impl.execute(
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\alembic\ddl\impl.py", line 217, in execute
    self._exec(sql, execution_options)
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\alembic\ddl\impl.py", line 210, in _exec
    return conn.execute(construct, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\sqlalchemy\engine\base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\sqlalchemy\sql\elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\sqlalchemy\engine\base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\sqlalchemy\engine\base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\sqlalchemy\engine\base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\sqlalchemy\engine\base.py", line 2355, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\sqlalchemy\engine\base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "C:\ProgramData\anaconda3\envs\gis\Lib\site-packages\sqlalchemy\engine\default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table temp already exists
[SQL: CREATE TABLE temp (id INTEGER PRIMARY KEY NOT NULL,flow_variable VARCHAR(100),aggregation_method VARCHAR(100),interval INTEGER);]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

[margrietpalm]

Tasks:

• [x] Fix for 222 and 224 and create hotfix for 224
• [x] Fix for 225 and release new 225 packages
• [x] Fix in 227 (ongoing development)

Suggested pattern: _temp_{revnr}_{uuid}

[margrietpalm]

Fix released in threedi-schema 0.224.4, 0.225.3 and 0.226.2

[leendertvanwolfswinkel]

Tested and works as expected