Drop table error when using sqlite 3

[emiliobasualdo]

Hi, I found a small error when using sqlite3.
Basically DROP VIEW IF EXISTS %(table)s CASCADE; is not syntactically correct in Sqlite3 because of the CASACE.
The problem was solved by overriding the drop_view() method in helper.py:46

Regards

(env) Pilos-MBP:covid-api pilo$ python3 manage.py migrate
Operations to perform:
Apply all migrations: admin, auth, contenttypes, core, sessions
Running migrations:
Applying core.0001_initial...Traceback (most recent call last):
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/db/backends/sqlite3/base.py", line 394, in execute
return Database.Cursor.execute(self, query)
sqlite3.OperationalError: near "CASCADE": syntax error

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

Traceback (most recent call last):
File "manage.py", line 21, in
main()
File "manage.py", line 17, in main
execute_from_command_line(sys.argv)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/core/management/init.py", line 401, in execute_from_command_line
utility.execute()
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/core/management/init.py", line 395, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/core/management/base.py", line 328, in run_from_argv
self.execute(*args, **cmd_options)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/core/management/base.py", line 369, in execute
output = self.handle(*args, **options)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/core/management/base.py", line 83, in wrapped
res = handle_func(*args, **kwargs)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 231, in handle
post_migrate_state = executor.migrate(
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/db/migrations/executor.py", line 117, in migrate
state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/db/migrations/executor.py", line 245, in apply_migration
state = migration.apply(state, schema_editor)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/db/migrations/migration.py", line 124, in apply
operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/dbview/helpers.py", line 18, in database_forwards
self._drop_view(fake_model, schema_editor)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/dbview/helpers.py", line 52, in _drop_view
schema_editor.execute(sql, None)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 142, in execute
cursor.execute(sql, params)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/db/backends/utils.py", line 100, in execute
return super().execute(sql, params)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/db/backends/utils.py", line 68, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/db/utils.py", line 90, in exit
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql)
File "/Users/pilo/development/itba/vizinfo/covid-api/env/lib/python3.8/site-packages/django/db/backends/sqlite3/base.py", line 394, in execute
return Database.Cursor.execute(self, query)
django.db.utils.OperationalError: near "CASCADE": syntax error

[moorchegue]

Hi, thanks for reporting this!

This change was initially added to support Postgres views and I checked MySQL documentation to make sure it's backwards compatible. I wasn't entirely sure that's widely supported, and now it's clear there're problems with this approach.

Let me see if different queries could be used for different engines without introducing too much complexity.

[moorchegue]

I think this should be resolved by this change. Please take a look.

Sorry it took so long!