Tests: configure in-memory database #81291

New Issue

Anna Sirota · 2020-09-29T13:30:04+02:00

Anna Sirota commented

2020-09-29 13:30:04 +02:00

Tests currently require PostgreSQL instance running, which makes them slow.
It would be better to use in-memory sqlite instead, however this requires database migrations reset and PostgreSQL-specific queries removed from them.

Tests currently require PostgreSQL instance running, which makes them slow. It would be better to use in-memory sqlite instead, however this requires database migrations reset and PostgreSQL-specific queries removed from them.

Anna Sirota self-assigned this 2020-09-29 13:30:04 +02:00

Anna Sirota commented

2020-09-29 13:30:04 +02:00

Added subscriber: @railla

Christoph Schmitz commented

2020-09-30 06:53:49 +02:00

Added subscriber: @chr.schmitz

Anna Sirota commented

2020-11-13 12:25:40 +01:00

There are quite a few changes that would be required to make current tests work with sqlite, not all of them are contained in the migrations and can be solved by a reset (which is pretty much impossible with a live production database):

some in migrations caused by lacking support for most ALTERs (1),
some due to sqlite-specific bugs in Django's migrations framework (2),
some due to PostgreSQL-specific query syntax or other syntax lacking support in sqlite (3)

After spending a few hours on this, I got past the issues from categories (1) and (2) to a point when tests were running, but failing due to (3).
At this point it's becoming clear that having tests run with sqlite while having postgresql production is pointless considering how many engine-specific problems there are so far.

Keeping the diff of changes required to get the tests running here for posterity, in case this gets abandoned after all.

diff --git a/films/migrations/0032_normalise_file_paths.py b/films/migrations/0032_normalise_file_paths.py
index 84b5067..79d17f1 100644
--- a/films/migrations/0032_normalise_file_paths.py
+++ b/films/migrations/0032_normalise_file_paths.py
@@ -9,47 +9,51 @@ class Migration(migrations.Migration):
         ('films', '0031_thumbnail_fields'),
     ]
 
+    # These are PostgreSQL-specific data migrations, kept here for posterity,
+    # but commented out because otherwise testing with sqlite3 doesn't work.
+    # These were used to normalise storage paths during migration to "Cloud v4"
+    # and don't do anything meaningful unless the database contains an data dump.
     operations = [
-        migrations.RunSQL(
-            r'''
-        update films_film
-        set logo = regexp_replace(logo, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3')
-        where logo like '_/%.%'
-        '''
-        ),
-        migrations.RunSQL(
-            r'''
-        update films_film
-        set thumbnail = regexp_replace(thumbnail, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3')
-        where thumbnail like '_/%.%'
-        '''
-        ),
-        migrations.RunSQL(
-            r'''
-        update films_film
-        set picture_header = regexp_replace(picture_header, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3')
-        where picture_header like '_/%.%'
-        '''
-        ),
-        migrations.RunSQL(
-            r'''
-        update films_film
-        set poster = regexp_replace(poster, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3')
-        where poster like '_/%.%'
-        '''
-        ),
-        migrations.RunSQL(
-            r'''
-        update films_collection
-        set preview = regexp_replace(preview, '[_/]*(\w{2})/(\w+)\.(\w+)', '\1/\2/\2.\3')
-        where preview like '_/%/%.%'
-        '''
-        ),
-        migrations.RunSQL(
-            r'''
-        update films_collection
-        set preview = regexp_replace(preview, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3')
-        where preview like '_/%.%'
-        '''
-        ),
+        # migrations.RunSQL(
+        #     r'''
+        # update films_film
+        # set logo = regexp_replace(logo, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3')
+        # where logo like '_/%.%'
+        # '''
+        # ),
+        # migrations.RunSQL(
+        #     r'''
+        # update films_film
+        # set thumbnail = regexp_replace(thumbnail, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3')
+        # where thumbnail like '_/%.%'
+        # '''
+        # ),
+        # migrations.RunSQL(
+        #     r'''
+        # update films_film
+        # set picture_header = regexp_replace(picture_header, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3')
+        # where picture_header like '_/%.%'
+        # '''
+        # ),
+        # migrations.RunSQL(
+        #     r'''
+        # update films_film
+        # set poster = regexp_replace(poster, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3')
+        # where poster like '_/%.%'
+        # '''
+        # ),
+        # migrations.RunSQL(
+        #     r'''
+        # update films_collection
+        # set preview = regexp_replace(preview, '[_/]*(\w{2})/(\w+)\.(\w+)', '\1/\2/\2.\3')
+        # where preview like '_/%/%.%'
+        # '''
+        # ),
+        # migrations.RunSQL(
+        #     r'''
+        # update films_collection
+        # set preview = regexp_replace(preview, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3')
+        # where preview like '_/%.%'
+        # '''
+        # ),
     ]
diff --git a/profiles/migrations/0004_enforce_unique_oauth_user_id.py b/profiles/migrations/0004_enforce_unique_oauth_user_id.py
index 49e2a5b..c8e0b9e 100644
--- a/profiles/migrations/0004_enforce_unique_oauth_user_id.py
+++ b/profiles/migrations/0004_enforce_unique_oauth_user_id.py
@@ -1,6 +1,40 @@
 # Generated by Django 3.0.9 on 2020-10-24 10:33
 
 from django.db import migrations, models
+import logging
+logger = logging.getLogger(__name__)
+
+
+def forwards(apps, schema_editor):
+    # sqlite does not support this alter https://www.sqlite.org/lang_altertable.html#otheralter
+    if not schema_editor.connection.vendor.startswith('postgresql'):
+        logger.info('Database vendor: {}'.format(schema_editor.connection.vendor))
+        logger.info('Skipping migration without attempting to ADD CONSTRAINT')
+        return
+
+    schema_editor.execute(
+        # Generated with sqlmigrate from an automatic migration that was removed right after
+        # ./manage.py sqlmigrate blender_id_oauth_client NNNN
+        'ALTER TABLE "blender_id_oauth_client_oauthuserinfo" '
+        'ADD CONSTRAINT "blender_id_oauth_client__oauth_user_id_b1e52371_uniq" '
+        'UNIQUE ("oauth_user_id");',
+        'CREATE INDEX "blender_id_oauth_client__oauth_user_id_b1e52371_like" '
+        'ON "blender_id_oauth_client_oauthuserinfo" ("oauth_user_id" varchar_pattern_ops);',
+    )
+
+
+def backwards(apps, schema_editor):
+    if not schema_editor.connection.vendor.startswith('postgresql'):
+        logger.info('Database vendor: {}'.format(schema_editor.connection.vendor))
+        logger.info('Skipping migration without attempting to DROP CONSTRAINT')
+        return
+
+    schema_editor.execute(
+        # ./manage.py sqlmigrate blender_id_oauth_client NNNN --backwards
+        'DROP INDEX IF EXISTS "blender_id_oauth_client__oauth_user_id_b1e52371_like";',
+        'ALTER TABLE "blender_id_oauth_client_oauthuserinfo" '
+        'DROP CONSTRAINT IF EXISTS "blender_id_oauth_client__oauth_user_id_b1e52371_uniq";',
+    )
 
 
 class Migration(migrations.Migration):
@@ -15,22 +49,5 @@ class Migration(migrations.Migration):
         - there's no other way to enforce this contraint
         - Addin OAuthUserInfo._meta.get_field('oauth_user_id')._unique = True
         # would generate a migration inside `blender_id_oauth_client`
-        migrations.RunSQL(
-            # Generated with sqlmigrate from an automatic migration that was removed right after
-            # e.g. if blender_id_oauth_client migration number is NNNN
-            sql=[
-                # ./manage.py sqlmigrate blender_id_oauth_client NNNN
-                'ALTER TABLE "blender_id_oauth_client_oauthuserinfo" '
-                'ADD CONSTRAINT "blender_id_oauth_client__oauth_user_id_b1e52371_uniq" '
-                'UNIQUE ("oauth_user_id");',
-                'CREATE INDEX "blender_id_oauth_client__oauth_user_id_b1e52371_like" '
-                'ON "blender_id_oauth_client_oauthuserinfo" ("oauth_user_id" varchar_pattern_ops);',
-            ],
-            reverse_sql=[
-                # ./manage.py sqlmigrate blender_id_oauth_client NNNN --backwards
-                'DROP INDEX IF EXISTS "blender_id_oauth_client__oauth_user_id_b1e52371_like";',
-                'ALTER TABLE "blender_id_oauth_client_oauthuserinfo" '
-                'DROP CONSTRAINT IF EXISTS "blender_id_oauth_client__oauth_user_id_b1e52371_uniq";',
-            ],
-        ),
+        migrations.RunPython(forwards, backwards, atomic=True)
     ]
diff --git a/static_assets/migrations/0018_normalise_file_paths.py b/static_assets/migrations/0018_normalise_file_paths.py
index 06b335e..3abac9d 100644
--- a/static_assets/migrations/0018_normalise_file_paths.py
+++ b/static_assets/migrations/0018_normalise_file_paths.py
@@ -9,33 +9,37 @@ class Migration(migrations.Migration):
         ('static_assets', '0017_drop_redundant_fields'),
     ]
 
+    # These are PostgreSQL-specific data migrations, kept here for posterity,
+    # but commented out because otherwise testing with sqlite3 doesn't work.
+    # These were used to normalise storage paths during migration to "Cloud v4"
+    # and don't do anything meaningful unless the database contains an data dump.
     operations = [
-        migrations.RunSQL(
-            r'''
-        update static_assets_staticasset
-        set thumbnail = regexp_replace(thumbnail, '[_/]*(\w{2})/(\w+)\.(\w+)', '\1/\2/\2.\3')
-        where thumbnail like '_/%/%.%'
-        '''
-        ),
-        migrations.RunSQL(
-            r'''
-        update static_assets_staticasset
-        set thumbnail = regexp_replace(thumbnail, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3')
-        where thumbnail like '_/%.%'
-        '''
-        ),
-        migrations.RunSQL(
-            r'''
-        update static_assets_staticasset
-        set source = regexp_replace(source, '[_/]*(\w{2})/(\w+)([0-9a-z-]*)\.(\w+)', '\1/\2/\2\3.\4')
-        where source like '_/%/%.%'
-        '''
-        ),
-        migrations.RunSQL(
-            r'''
-        update static_assets_staticasset
-        set source = regexp_replace(source, '[_/]*(\w{2})(\w+)([0-9a-z-]*)\.(\w+)', '\1/\1\2/\1\2\3.\4')
-        where source not like '_/%/%.%' and source like '_/%'
-        '''
-        ),
+        # migrations.RunSQL(
+        #     r'''
+        # update static_assets_staticasset
+        # set thumbnail = regexp_replace(thumbnail, '[_/]*(\w{2})/(\w+)\.(\w+)', '\1/\2/\2.\3')
+        # where thumbnail like '_/%/%.%'
+        # '''
+        # ),
+        # migrations.RunSQL(
+        #     r'''
+        # update static_assets_staticasset
+        # set thumbnail = regexp_replace(thumbnail, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3')
+        # where thumbnail like '_/%.%'
+        # '''
+        # ),
+        # migrations.RunSQL(
+        #     r'''
+        # update static_assets_staticasset
+        # set source = regexp_replace(source, '[_/]*(\w{2})/(\w+)([0-9a-z-]*)\.(\w+)', '\1/\2/\2\3.\4')
+        # where source like '_/%/%.%'
+        # '''
+        # ),
+        # migrations.RunSQL(
+        #     r'''
+        # update static_assets_staticasset
+        # set source = regexp_replace(source, '[_/]*(\w{2})(\w+)([0-9a-z-]*)\.(\w+)', '\1/\1\2/\1\2\3.\4')
+        # where source not like '_/%/%.%' and source like '_/%'
+        # '''
+        # ),
     ]
diff --git a/training/migrations/0018_training_and_section_tags.py b/training/migrations/0018_training_and_section_tags.py
index 3f42d79..e577ed6 100644
--- a/training/migrations/0018_training_and_section_tags.py
+++ b/training/migrations/0018_training_and_section_tags.py
@@ -12,6 +12,10 @@ class Migration(migrations.Migration):
     ]
 
     operations = [
+        # Without this migration fails on sqlite with FieldDoesNotExist 'tag'
+        # see https://code.djangoproject.com/ticket/29124
+        # and https://code.djangoproject.com/ticket/28862
+        migrations.RemoveConstraint(model_name='trainingtag', name='unique_tags_per_training'),
         migrations.RemoveField(model_name='trainingtag', name='tag',),
         migrations.RemoveField(model_name='trainingtag', name='training',),
         migrations.AddField(
diff --git a/training/migrations/0023_normalise_file_paths.py b/training/migrations/0023_normalise_file_paths.py
index c12f83a..d3ce360 100644
--- a/training/migrations/0023_normalise_file_paths.py
+++ b/training/migrations/0023_normalise_file_paths.py
@@ -9,40 +9,44 @@ class Migration(migrations.Migration):
         ('training', '0022_training_thumbnail'),
     ]
 
+    # These are PostgreSQL-specific data migrations, kept here for posterity,
+    # but commented out because otherwise testing with sqlite3 doesn't work.
+    # These were used to normalise storage paths during migration to "Cloud v4"
+    # and don't do anything meaningful unless the database contains an data dump.
     operations = [
-        migrations.RunSQL(
-            r'''
-        update training_asset
-        set file = regexp_replace(file, '[_/]*(\w{2})(\w+).(\w+)', '\1/\1\2/\1\2.\3')
-        where file like '_/%.%'
-        '''
-        ),
-        migrations.RunSQL(
-            r'''
-        update training_video
-        set file = regexp_replace(file, '[_/]*(\w{2})([0-9a-z]+)([0-9a-z-]*)\.(\w+)', '\1/\1\2/\1\2\3.\4')
-        where file like '_/%'
-        '''
-        ),  # _/7923ce30edc04ad496a45a4ae08b4e9f-1080p.mp4 or _/d04734b40312cc78eb6ed41b725efd92e250d6be.mp4
-        migrations.RunSQL(
-            r'''
-        update training_training
-        set thumbnail = regexp_replace(thumbnail, '[_/]*(\w{2})(\w+).(\w+)', '\1/\1\2/\1\2.\3')
-        where thumbnail like '_/%'
-        '''
-        ),
-        migrations.RunSQL(
-            r'''
-        update training_training
-        set picture_header = regexp_replace(picture_header, '[_/]*\w{2}/(\w{2})(\w+).(\w+)', '\1/\1\2/\1\2.\3')
-        where picture_header like '_/%/%.%'
-        '''
-        ),  # _/48/4835e54b1bf9197dbd13fd10ea4a9db3.jpg
-        migrations.RunSQL(
-            r'''
-        update training_training
-        set picture_header = regexp_replace(picture_header, '[_/]*(\w{2})(\w+).(\w+)', '\1/\1\2/\1\2.\3')
-        where picture_header like '_/%' and picture_header not like '_/%/%.%';
-        '''
-        ),
+        # migrations.RunSQL(
+        #     r'''
+        # update training_asset
+        # set file = regexp_replace(file, '[_/]*(\w{2})(\w+).(\w+)', '\1/\1\2/\1\2.\3')
+        # where file like '_/%.%'
+        # '''
+        # ),
+        # migrations.RunSQL(
+        #     r'''
+        # update training_video
+        # set file = regexp_replace(file, '[_/]*(\w{2})([0-9a-z]+)([0-9a-z-]*)\.(\w+)', '\1/\1\2/\1\2\3.\4')
+        # where file like '_/%'
+        # '''
+        # ),  # _/7923ce30edc04ad496a45a4ae08b4e9f-1080p.mp4 or _/d04734b40312cc78eb6ed41b725efd92e250d6be.mp4
+        # migrations.RunSQL(
+        #     r'''
+        # update training_training
+        # set thumbnail = regexp_replace(thumbnail, '[_/]*(\w{2})(\w+).(\w+)', '\1/\1\2/\1\2.\3')
+        # where thumbnail like '_/%'
+        # '''
+        # ),
+        # migrations.RunSQL(
+        #     r'''
+        # update training_training
+        # set picture_header = regexp_replace(picture_header, '[_/]*\w{2}/(\w{2})(\w+).(\w+)', '\1/\1\2/\1\2.\3')
+        # where picture_header like '_/%/%.%'
+        # '''
+        # ),  # _/48/4835e54b1bf9197dbd13fd10ea4a9db3.jpg
+        # migrations.RunSQL(
+        #     r'''
+        # update training_training
+        # set picture_header = regexp_replace(picture_header, '[_/]*(\w{2})(\w+).(\w+)', '\1/\1\2/\1\2.\3')
+        # where picture_header like '_/%' and picture_header not like '_/%/%.%';
+        # '''
+        # ),
     ]
diff --git a/training/migrations/0027_training_static_assets_refactor.py b/training/migrations/0027_training_static_assets_refactor.py
index 3c737d2..dc9c6d8 100644
--- a/training/migrations/0027_training_static_assets_refactor.py
+++ b/training/migrations/0027_training_static_assets_refactor.py
@@ -10,6 +10,10 @@ class Migration(migrations.Migration):
     ]
 
     operations = [
+        # Without this migration fails on sqlite with FieldDoesNotExist 'user'
+        # see https://code.djangoproject.com/ticket/29124
+        # and https://code.djangoproject.com/ticket/28862
+        migrations.RemoveConstraint(model_name='uservideoprogress', name='unique_progress_per_user_and_video_'),
         migrations.RemoveField(model_name='uservideoprogress', name='user',),
         migrations.RemoveField(model_name='uservideoprogress', name='video',),
         migrations.RemoveField(model_name='video', name='section',),

There are quite a few changes that would be required to make current tests work with sqlite, not all of them are contained in the migrations and can be solved by a reset (which is pretty much impossible with a live production database): some in migrations caused by lacking support for most `ALTER`s (1), some due to sqlite-specific bugs in Django's migrations framework (2), some due to PostgreSQL-specific query syntax or other syntax lacking support in sqlite (3) After spending a few hours on this, I got past the issues from categories (1) and (2) to a point when tests were running, but failing due to (3). At this point it's becoming clear that having tests run with sqlite while having postgresql production is pointless considering how many engine-specific problems there are so far. Keeping the diff of changes required to get the tests running here for posterity, in case this gets abandoned after all. ``` diff --git a/films/migrations/0032_normalise_file_paths.py b/films/migrations/0032_normalise_file_paths.py index 84b5067..79d17f1 100644 --- a/films/migrations/0032_normalise_file_paths.py +++ b/films/migrations/0032_normalise_file_paths.py @@ -9,47 +9,51 @@ class Migration(migrations.Migration): ('films', '0031_thumbnail_fields'), ] + # These are PostgreSQL-specific data migrations, kept here for posterity, + # but commented out because otherwise testing with sqlite3 doesn't work. + # These were used to normalise storage paths during migration to "Cloud v4" + # and don't do anything meaningful unless the database contains an data dump. operations = [ - migrations.RunSQL( - r''' - update films_film - set logo = regexp_replace(logo, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3') - where logo like '_/%.%' - ''' - ), - migrations.RunSQL( - r''' - update films_film - set thumbnail = regexp_replace(thumbnail, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3') - where thumbnail like '_/%.%' - ''' - ), - migrations.RunSQL( - r''' - update films_film - set picture_header = regexp_replace(picture_header, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3') - where picture_header like '_/%.%' - ''' - ), - migrations.RunSQL( - r''' - update films_film - set poster = regexp_replace(poster, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3') - where poster like '_/%.%' - ''' - ), - migrations.RunSQL( - r''' - update films_collection - set preview = regexp_replace(preview, '[_/]*(\w{2})/(\w+)\.(\w+)', '\1/\2/\2.\3') - where preview like '_/%/%.%' - ''' - ), - migrations.RunSQL( - r''' - update films_collection - set preview = regexp_replace(preview, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3') - where preview like '_/%.%' - ''' - ), + # migrations.RunSQL( + # r''' + # update films_film + # set logo = regexp_replace(logo, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3') + # where logo like '_/%.%' + # ''' + # ), + # migrations.RunSQL( + # r''' + # update films_film + # set thumbnail = regexp_replace(thumbnail, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3') + # where thumbnail like '_/%.%' + # ''' + # ), + # migrations.RunSQL( + # r''' + # update films_film + # set picture_header = regexp_replace(picture_header, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3') + # where picture_header like '_/%.%' + # ''' + # ), + # migrations.RunSQL( + # r''' + # update films_film + # set poster = regexp_replace(poster, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3') + # where poster like '_/%.%' + # ''' + # ), + # migrations.RunSQL( + # r''' + # update films_collection + # set preview = regexp_replace(preview, '[_/]*(\w{2})/(\w+)\.(\w+)', '\1/\2/\2.\3') + # where preview like '_/%/%.%' + # ''' + # ), + # migrations.RunSQL( + # r''' + # update films_collection + # set preview = regexp_replace(preview, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3') + # where preview like '_/%.%' + # ''' + # ), ] diff --git a/profiles/migrations/0004_enforce_unique_oauth_user_id.py b/profiles/migrations/0004_enforce_unique_oauth_user_id.py index 49e2a5b..c8e0b9e 100644 --- a/profiles/migrations/0004_enforce_unique_oauth_user_id.py +++ b/profiles/migrations/0004_enforce_unique_oauth_user_id.py @@ -1,6 +1,40 @@ # Generated by Django 3.0.9 on 2020-10-24 10:33 from django.db import migrations, models +import logging +logger = logging.getLogger(__name__) + + +def forwards(apps, schema_editor): + # sqlite does not support this alter https://www.sqlite.org/lang_altertable.html#otheralter + if not schema_editor.connection.vendor.startswith('postgresql'): + logger.info('Database vendor: {}'.format(schema_editor.connection.vendor)) + logger.info('Skipping migration without attempting to ADD CONSTRAINT') + return + + schema_editor.execute( + # Generated with sqlmigrate from an automatic migration that was removed right after + # ./manage.py sqlmigrate blender_id_oauth_client NNNN + 'ALTER TABLE "blender_id_oauth_client_oauthuserinfo" ' + 'ADD CONSTRAINT "blender_id_oauth_client__oauth_user_id_b1e52371_uniq" ' + 'UNIQUE ("oauth_user_id");', + 'CREATE INDEX "blender_id_oauth_client__oauth_user_id_b1e52371_like" ' + 'ON "blender_id_oauth_client_oauthuserinfo" ("oauth_user_id" varchar_pattern_ops);', + ) + + +def backwards(apps, schema_editor): + if not schema_editor.connection.vendor.startswith('postgresql'): + logger.info('Database vendor: {}'.format(schema_editor.connection.vendor)) + logger.info('Skipping migration without attempting to DROP CONSTRAINT') + return + + schema_editor.execute( + # ./manage.py sqlmigrate blender_id_oauth_client NNNN --backwards + 'DROP INDEX IF EXISTS "blender_id_oauth_client__oauth_user_id_b1e52371_like";', + 'ALTER TABLE "blender_id_oauth_client_oauthuserinfo" ' + 'DROP CONSTRAINT IF EXISTS "blender_id_oauth_client__oauth_user_id_b1e52371_uniq";', + ) class Migration(migrations.Migration): @@ -15,22 +49,5 @@ class Migration(migrations.Migration): - there's no other way to enforce this contraint - Addin OAuthUserInfo._meta.get_field('oauth_user_id')._unique = True # would generate a migration inside `blender_id_oauth_client` - migrations.RunSQL( - # Generated with sqlmigrate from an automatic migration that was removed right after - # e.g. if blender_id_oauth_client migration number is NNNN - sql=[ - # ./manage.py sqlmigrate blender_id_oauth_client NNNN - 'ALTER TABLE "blender_id_oauth_client_oauthuserinfo" ' - 'ADD CONSTRAINT "blender_id_oauth_client__oauth_user_id_b1e52371_uniq" ' - 'UNIQUE ("oauth_user_id");', - 'CREATE INDEX "blender_id_oauth_client__oauth_user_id_b1e52371_like" ' - 'ON "blender_id_oauth_client_oauthuserinfo" ("oauth_user_id" varchar_pattern_ops);', - ], - reverse_sql=[ - # ./manage.py sqlmigrate blender_id_oauth_client NNNN --backwards - 'DROP INDEX IF EXISTS "blender_id_oauth_client__oauth_user_id_b1e52371_like";', - 'ALTER TABLE "blender_id_oauth_client_oauthuserinfo" ' - 'DROP CONSTRAINT IF EXISTS "blender_id_oauth_client__oauth_user_id_b1e52371_uniq";', - ], - ), + migrations.RunPython(forwards, backwards, atomic=True) ] diff --git a/static_assets/migrations/0018_normalise_file_paths.py b/static_assets/migrations/0018_normalise_file_paths.py index 06b335e..3abac9d 100644 --- a/static_assets/migrations/0018_normalise_file_paths.py +++ b/static_assets/migrations/0018_normalise_file_paths.py @@ -9,33 +9,37 @@ class Migration(migrations.Migration): ('static_assets', '0017_drop_redundant_fields'), ] + # These are PostgreSQL-specific data migrations, kept here for posterity, + # but commented out because otherwise testing with sqlite3 doesn't work. + # These were used to normalise storage paths during migration to "Cloud v4" + # and don't do anything meaningful unless the database contains an data dump. operations = [ - migrations.RunSQL( - r''' - update static_assets_staticasset - set thumbnail = regexp_replace(thumbnail, '[_/]*(\w{2})/(\w+)\.(\w+)', '\1/\2/\2.\3') - where thumbnail like '_/%/%.%' - ''' - ), - migrations.RunSQL( - r''' - update static_assets_staticasset - set thumbnail = regexp_replace(thumbnail, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3') - where thumbnail like '_/%.%' - ''' - ), - migrations.RunSQL( - r''' - update static_assets_staticasset - set source = regexp_replace(source, '[_/]*(\w{2})/(\w+)([0-9a-z-]*)\.(\w+)', '\1/\2/\2\3.\4') - where source like '_/%/%.%' - ''' - ), - migrations.RunSQL( - r''' - update static_assets_staticasset - set source = regexp_replace(source, '[_/]*(\w{2})(\w+)([0-9a-z-]*)\.(\w+)', '\1/\1\2/\1\2\3.\4') - where source not like '_/%/%.%' and source like '_/%' - ''' - ), + # migrations.RunSQL( + # r''' + # update static_assets_staticasset + # set thumbnail = regexp_replace(thumbnail, '[_/]*(\w{2})/(\w+)\.(\w+)', '\1/\2/\2.\3') + # where thumbnail like '_/%/%.%' + # ''' + # ), + # migrations.RunSQL( + # r''' + # update static_assets_staticasset + # set thumbnail = regexp_replace(thumbnail, '[_/]*(\w{2})(\w+)\.(\w+)', '\1/\1\2/\1\2.\3') + # where thumbnail like '_/%.%' + # ''' + # ), + # migrations.RunSQL( + # r''' + # update static_assets_staticasset + # set source = regexp_replace(source, '[_/]*(\w{2})/(\w+)([0-9a-z-]*)\.(\w+)', '\1/\2/\2\3.\4') + # where source like '_/%/%.%' + # ''' + # ), + # migrations.RunSQL( + # r''' + # update static_assets_staticasset + # set source = regexp_replace(source, '[_/]*(\w{2})(\w+)([0-9a-z-]*)\.(\w+)', '\1/\1\2/\1\2\3.\4') + # where source not like '_/%/%.%' and source like '_/%' + # ''' + # ), ] diff --git a/training/migrations/0018_training_and_section_tags.py b/training/migrations/0018_training_and_section_tags.py index 3f42d79..e577ed6 100644 --- a/training/migrations/0018_training_and_section_tags.py +++ b/training/migrations/0018_training_and_section_tags.py @@ -12,6 +12,10 @@ class Migration(migrations.Migration): ] operations = [ + # Without this migration fails on sqlite with FieldDoesNotExist 'tag' + # see https://code.djangoproject.com/ticket/29124 + # and https://code.djangoproject.com/ticket/28862 + migrations.RemoveConstraint(model_name='trainingtag', name='unique_tags_per_training'), migrations.RemoveField(model_name='trainingtag', name='tag',), migrations.RemoveField(model_name='trainingtag', name='training',), migrations.AddField( diff --git a/training/migrations/0023_normalise_file_paths.py b/training/migrations/0023_normalise_file_paths.py index c12f83a..d3ce360 100644 --- a/training/migrations/0023_normalise_file_paths.py +++ b/training/migrations/0023_normalise_file_paths.py @@ -9,40 +9,44 @@ class Migration(migrations.Migration): ('training', '0022_training_thumbnail'), ] + # These are PostgreSQL-specific data migrations, kept here for posterity, + # but commented out because otherwise testing with sqlite3 doesn't work. + # These were used to normalise storage paths during migration to "Cloud v4" + # and don't do anything meaningful unless the database contains an data dump. operations = [ - migrations.RunSQL( - r''' - update training_asset - set file = regexp_replace(file, '[_/]*(\w{2})(\w+).(\w+)', '\1/\1\2/\1\2.\3') - where file like '_/%.%' - ''' - ), - migrations.RunSQL( - r''' - update training_video - set file = regexp_replace(file, '[_/]*(\w{2})([0-9a-z]+)([0-9a-z-]*)\.(\w+)', '\1/\1\2/\1\2\3.\4') - where file like '_/%' - ''' - ), # _/7923ce30edc04ad496a45a4ae08b4e9f-1080p.mp4 or _/d04734b40312cc78eb6ed41b725efd92e250d6be.mp4 - migrations.RunSQL( - r''' - update training_training - set thumbnail = regexp_replace(thumbnail, '[_/]*(\w{2})(\w+).(\w+)', '\1/\1\2/\1\2.\3') - where thumbnail like '_/%' - ''' - ), - migrations.RunSQL( - r''' - update training_training - set picture_header = regexp_replace(picture_header, '[_/]*\w{2}/(\w{2})(\w+).(\w+)', '\1/\1\2/\1\2.\3') - where picture_header like '_/%/%.%' - ''' - ), # _/48/4835e54b1bf9197dbd13fd10ea4a9db3.jpg - migrations.RunSQL( - r''' - update training_training - set picture_header = regexp_replace(picture_header, '[_/]*(\w{2})(\w+).(\w+)', '\1/\1\2/\1\2.\3') - where picture_header like '_/%' and picture_header not like '_/%/%.%'; - ''' - ), + # migrations.RunSQL( + # r''' + # update training_asset + # set file = regexp_replace(file, '[_/]*(\w{2})(\w+).(\w+)', '\1/\1\2/\1\2.\3') + # where file like '_/%.%' + # ''' + # ), + # migrations.RunSQL( + # r''' + # update training_video + # set file = regexp_replace(file, '[_/]*(\w{2})([0-9a-z]+)([0-9a-z-]*)\.(\w+)', '\1/\1\2/\1\2\3.\4') + # where file like '_/%' + # ''' + # ), # _/7923ce30edc04ad496a45a4ae08b4e9f-1080p.mp4 or _/d04734b40312cc78eb6ed41b725efd92e250d6be.mp4 + # migrations.RunSQL( + # r''' + # update training_training + # set thumbnail = regexp_replace(thumbnail, '[_/]*(\w{2})(\w+).(\w+)', '\1/\1\2/\1\2.\3') + # where thumbnail like '_/%' + # ''' + # ), + # migrations.RunSQL( + # r''' + # update training_training + # set picture_header = regexp_replace(picture_header, '[_/]*\w{2}/(\w{2})(\w+).(\w+)', '\1/\1\2/\1\2.\3') + # where picture_header like '_/%/%.%' + # ''' + # ), # _/48/4835e54b1bf9197dbd13fd10ea4a9db3.jpg + # migrations.RunSQL( + # r''' + # update training_training + # set picture_header = regexp_replace(picture_header, '[_/]*(\w{2})(\w+).(\w+)', '\1/\1\2/\1\2.\3') + # where picture_header like '_/%' and picture_header not like '_/%/%.%'; + # ''' + # ), ] diff --git a/training/migrations/0027_training_static_assets_refactor.py b/training/migrations/0027_training_static_assets_refactor.py index 3c737d2..dc9c6d8 100644 --- a/training/migrations/0027_training_static_assets_refactor.py +++ b/training/migrations/0027_training_static_assets_refactor.py @@ -10,6 +10,10 @@ class Migration(migrations.Migration): ] operations = [ + # Without this migration fails on sqlite with FieldDoesNotExist 'user' + # see https://code.djangoproject.com/ticket/29124 + # and https://code.djangoproject.com/ticket/28862 + migrations.RemoveConstraint(model_name='uservideoprogress', name='unique_progress_per_user_and_video_'), migrations.RemoveField(model_name='uservideoprogress', name='user',), migrations.RemoveField(model_name='uservideoprogress', name='video',), migrations.RemoveField(model_name='video', name='section',), ```

Sign in to join this conversation.